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

    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
          Gysbert Wassenaar

          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.