2 Replies Latest reply: May 9, 2013 11:08 AM by Benjamin Dykstra

# Sum of Different Date Ranges

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.

• ###### Re: Sum of Different Date Ranges

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

• ###### Re: Sum of Different Date Ranges

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.