Sum of Different Date Ranges

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

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))

[#3]

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.