# Thread: Calculating prior year-to-date figures on report

1. Junior Member
Join Date
Feb 2002
Posts
7

## Unanswered: Calculating prior year-to-date figures on report

Hi,

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".

Thanks!!

Red.

2. Registered User
Join Date
Nov 2001
Posts
336
Hi,

The best way to get "unstuck" is to re-design your table. Instead of having month of sale in columns you'd better have them in rows.

Like this:

1. SaleID
2. DateOfSale
3. AmountOfSale

HTH

3. Junior Member
Join Date
Feb 2002
Posts
7
Hi,

Well, I'm not sure how I could do that since the data is coming from another database (via ODBC links), and I can't manipulate it...

Maybe there is a way that I'm not aware of?

Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•