Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlikview Community:
I'm trying to do a sum of sales for a number of different stores. Each store has a different date range that I would like to sum. When drilled down to a single store, the date range/sum is correct; however, when I am viewing all of the stores at once, the expression is using the max date (01/22/2013) for all of the stores--leading to incorrect totals for both the stores and the overall sum.
Stores/Date Ranges:
0111 02/04/2012 - 01/22/2013
0222 02/04/2012 - 01/21/2013
0333 02/04/2012 - 01/17/2013
0444 02/04/2012 - 01/15/2013
0555 02/04/2012 - 01/10/2013
0666 02/04/2012 - 01/09/2013
Expression:
Sum({$<POSDate={">=$(=$(vMinSIRDate))<=$(=Date($(vSIRRunDate)))"}>}SalesDol)
POSDate = date range field
vMinSIRDate = 02/04/2012 (no issues with this variable)
vSIRRunDate = Max(RunDate)
My thinking was that this could be solved with a simple aggregation: Aggr(Sum(SalesDol),StoreNo)
but this does not work either--it uses 01/22/2013 for all of the stores as well. I also tried Aggregation on the Max(RunDate) and a number of different combinations of these two, but I can't get it to work correctly.
Any advice would be greatly appreciated.
Sincerely,
Ben D.
You can't easily use set analysis expressions in this case. The sets are calculated per chart, not per row. So you get one set for all the stores instead of a set per store like you want. You can use if statements instead. Something like:
Sum( if (POSDate >= $(vMinSIRDate) and POSDATE <= max(RunDate),SalesDol))
You can't easily use set analysis expressions in this case. The sets are calculated per chart, not per row. So you get one set for all the stores instead of a set per store like you want. You can use if statements instead. Something like:
Sum( if (POSDate >= $(vMinSIRDate) and POSDATE <= max(RunDate),SalesDol))
Great, thank you Gysbert!
I had to make some slight modifications, but that was the answer I was looking for.
Sum(If(POSDate >= Date($(vMinSIRDate)) and POSDate <= Date(RunDate),Aggr(Max(SalesDol),ItemKey,POSDate)))
Without the aggregation and max on the sales dollars, I was getting extremely high numbers--multiple rows were being summed for each ItemKey/POSDate.
Thanks again!
Ben D.