Possible to Get prior records?

  • Thread starter Thread starter sara
  • Start date Start date
S

sara

I have to do a rolling 12-month average of sales. The
problem is that our Month Ending Dates are not standard.
I created a table that is FYNumber, MonthNumber,
MonthStartDate, MonthEndDate, but I can't figure out how
to use it. I'm thinking like a VLookup in Excel-type
function, but just can't figure it out.

If the user can give me the first Year/Month combo, how
can I get the other 11 dates, so I can get their sales
(for each of our 15 stors), to calculate the rolling
average?

I'm stumped on this one and hope someone can get me
started.
thanks
Sara
 
Sara,

What if you added an extra field in the table you describe, to hold a serial
number for each period (without gaps)? That way, if a user wants to see a
report on period, say, X, the rolling 12-month average will be on periods
X-11 to X, which is easy to handle.

HTH,
Nikos
 
Thanks. I am sorry this took a while; I was out of the
office for a few days.

I did this - thanks. It seems a little clunky, but it
works. It took me 4 queries (1 get ending date; 2nd get
starting date; 3rd get all the data for each date between
starting and ending (364 days; 17 hours per day; 16
stores); 4th calculate the averages.

If this can be cleaned up at all, please let me know how.
Maybe it's ok this way. I hope this is what the boss
wants, too!

Thank you again,
Sara
 
Sara,

Not all clear to me, but I sense it could be simplified... Let me check if I
get this correctly:
You have a table with sales records (amount, store, date, period etc.);
You need to calculate averages per store over a 12-month period ending a
certain sales period;
If the above are accurate, then you only need one Totals query on your sales
table, with Total functions Group By on store, Avg on sales and Where on
period. The criterion on period could be something like:
= ([Enter ending period] - 11) And <= [Enter ending period]
Click to expand...
so the user only keys in the period number once when running the query.

HTH,
Nikos


are you trying to get averages per store for a 12-month period
 
Back
Top