Unanswered: Calculating prior year-to-date figures on report
I'm working on creating a report and I have run into a problem, which at
least to me is complicated, however I hope that for someone out there it's a piece of cake.
Let me first try to explain what I have to work from. (I get the data from another db via odbc, so i have no control over that part).
I'm linking to a table containing sales figures. The column headings are:
"mth_1" indicating previous month sales
"mth_2" indicating second previous month sales
and so forth until "mth_24" (the 24 previous months sales figures)
If we are in Feb 2002 "mth_1" would indicate Jan 2002 and "mth_2" would indicate Dec 2001 and so forth.
Hope I'm making sense so far.
Now, I'm trying to come up with a formula that will calculate the previous
year-to-date sales like such:
If we are in March 2002 I want to calculate the sum of March 2001, Feb 2001, and Jan 2001. (I already have the current year-to-date sales figures to compare with, just not prior year-to-date figures).
So, to accomplish the above example (if the current month was March 2002) I would take the sum of "mth_12" (march 2001), "mth_13"(feb 2001) and mth_14(jan 2001) to get the prior year-to-date sales.
Or let's say the current month is Jun 2002, then I would take mth_12(jun
2001), mth_13 (may 2001), mth_14 (apr 2001), mth_15(mar 2001), mth_16(feb 2001) and finally mth_17 (jan 2001) to get the prior year-to-date figures.
The big problem (at least to me) is that I need a formula that will do this
"on the fly". A formula that would check what the current month is and then calculate the corresponding previous months figures to come up with the prior year-to-date figures.
I'm guessing I could use an If...Then...Else code like something like this (obviusly I don't know the syntax and to top it off I also don't know where to put the code once I have it):
if month="january" then sum([mnt_12]) else
if month="february" then sum([mth_12]+[mth_13]) else
I'm stuck. Hopefully someone can help me get "unstuck".