I'm having an issue I need help with. I'm trying to get a report that shows current year sales compared to last years sales. The sales data is in a access table by week, weeks 1-52. The issue becomes the sales for last year, since there is sales data for the prior year the report shows all 52 weeks for both current year and prior year with the current year being 0 if there isn't any data for that week. I want to see comparable sales though so I'm only looking at sales amounts for 2009 if there is sales for 2010. I tried the following formula If([2010 Sales]>0,sum({$<FiscalYear={'2009'>}Sales),0) The formula works for each individual week, but when I want to see the totals by store the sum includes all sales for 2009. Any suggestions. Thanks
It seems like it should be simpler, but this worked for me with fake data in a pivot table:
sum(aggr(if(sum({1} total <Week> if(Year=max({1} total Year),Sales)),sum(Sales)),Year,Week))
See attached.
Edit: The Forum has decided to not let me attach files today, so below is the script, and I made a pivot table with Year and Week as my dimensions, and set it to indent mode so that I could see the subtotals.
LOAD 2009 as Year ,recno() as Week ,ceil(rand()*100) as Sales AUTOGENERATE 52 ; CONCATENATE LOAD 2010 as Year ,recno() as Week ,ceil(rand()*100) as Sales AUTOGENERATE 10 ;
This is closer to what I am looking for but not exact. Let's say I'm looking at sales by state. If the max number of weeks reported for New York is 10 then this formula gives me the sales in 2009 for all states up to week 10, even though other states have only reported thru Week 7. I need to sum 2009 sales by state for the week they have reported through for 2010.
