topic Comparitve Sales in QlikView

Comparitve Sales

Tue, 05 Jan 2010 18:38:54 GMT

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

Comparitve Sales

Wed, 06 Jan 2010 17:17:18 GMT

Any Suggestions?

Comparitve Sales

johnw — Wed, 06 Jan 2010 22:34:56 GMT

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
;

Comparitve Sales

Thu, 07 Jan 2010 22:27:59 GMT

I tried to duplicate it and it's not working. Here are my current formulas

2010 sales

sum({$<FiscalYear={'2010'}>} Sales)

2009 sales

sum({$<FiscalYear={'2009'},WeekNumber={"<=$(=max( {$<FiscalYear={'2010'}>} WeekNumber))"}>} Sales)

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.

Thanks