topic Sum of Different Date Ranges in QlikView

Sum of Different Date Ranges

Thu, 09 May 2013 12:45:44 GMT

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

Gysbert_Wassenaar — Thu, 09 May 2013 13:06:53 GMT

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

Thu, 09 May 2013 15:08:13 GMT

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.