2 Replies Latest reply: Aug 19, 2015 10:08 AM by Jason Bittle RSS

    Need help with grouping like data that has sequential gaps

      Hi all,

       

      I'm not even sure the best way to title this question, but I'm a bit stumped on how to resolve this.  I am trying to build a grid that groups similar data (in this case, quantity, value, rate) and sum a value (accrual).  The current rub is that I need to group the data in such a way that as long as the dimensions match, then we sum up the row.  As soon as any of the dimensions change, however, a new group by row needs to be be created.   Hopefully my examples will better describe how this should work.

       

      Here is the following raw data.   In this case, the accrual is an expression that's calculated (Value * (Rate/100)) / 360

           

      DateQuantityValue RateAccrual
      01-Jul-2015150,000487,500.00-10.00-135.42
      02-Jul-2015150,000487,500.00-10.00-135.42
      03-Jul-2015150,000487,500.00-10.00-135.42
      04-Jul-2015150,000487,500.00-10.00-135.42
      05-Jul-2015150,000487,500.00-10.00-135.42
      06-Jul-2015150,000525,000.00-10.00-145.83
      07-Jul-2015150,000487,500.00-10.00-135.42
      08-Jul-2015150,000487,500.00-10.00-135.42
      09-Jul-2015150,000450,000.00-10.00-125.00
      10-Jul-2015150,000450,000.00-10.00-125.00
      11-Jul-2015150,000450,000.00-10.00-125.00
      12-Jul-2015150,000450,000.00-10.00-125.00
      13-Jul-2015150,000450,000.00-10.00-125.00
      14-Jul-2015150,000487,500.00-10.00-135.42
      15-Jul-2015150,000487,500.00-10.00-135.42
      16-Jul-2015150,000487,500.00-10.00-135.42
      17-Jul-2015150,000450,000.00-10.00-125.00
      18-Jul-2015150,000450,000.00-10.00-125.00
      19-Jul-2015150,000450,000.00-10.00-125.00
      20-Jul-2015150,000450,000.00-10.00-125.00

       

      Ideally the data should look something similar to this:

              

      Start DateEnd Date# DaysQuantityValueRateTotal AccrualAvg Daily Accrual
      01-Jul-1506-Jul-155150,000487,500.00-10.00677.08135.42
      06-Jul-1507-Jul-151150,000525,000.00-10.00145.83145.83
      07-Jul-1509-Jul-152150,000487,500.00-10.00270.83135.42
      09-Jul-1514-Jul-155150,000450,000.00-10.00625.00125.00
      14-Jul-1517-Jul-153150,000487,500.00-10.00406.25135.42
      17-Jul-1521-Jul-154150,000450,000.00-10.00500.00125.00

       

      Here is what I was able to get working - I've set up my dimensions for Quantity, Value, and Rate.  Start Date and End date are just expressions which return the min/max of the Date field above, # of days is a count(date), and total/daily accruals are expressions which sum and sum/count the accrual calculated field.

              

      Start DateEnd Date# DaysQuantityValueRateTotal AccrualDaily Accrual
      01-Jul-1517-Jul-1510150,000487,500.00-10.001,354.17135.42
      06-Jul-1507-Jul-151150,000525,000.00-10.00145.83145.83
      09-Jul-1521-Jul-159150,000450,000.00-10.001,125.00125.00

       

      I'm looking for any tips or functions I could use to "divide/break" the grouping of data sequentially based upon date and whenever the quantity/value/rate changes compared to the record(s) before it.   I've looked into the help for the above/below function, but I haven't been able to get it working my case.  If I have to, I can probably work up some script that does all of this looping, but it feels like this is something Qlikview should be able to do.

       

      Any help would be greatly appreciated - even pointers to the correct function would be great.

       

      Thanks in advance, let me know if I can further clarify anything.

       

      cheers,

      --jb