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
| Date | Quantity | Value | Rate | Accrual |
| 01-Jul-2015 | 150,000 | 487,500.00 | -10.00 | -135.42 |
| 02-Jul-2015 | 150,000 | 487,500.00 | -10.00 | -135.42 |
| 03-Jul-2015 | 150,000 | 487,500.00 | -10.00 | -135.42 |
| 04-Jul-2015 | 150,000 | 487,500.00 | -10.00 | -135.42 |
| 05-Jul-2015 | 150,000 | 487,500.00 | -10.00 | -135.42 |
| 06-Jul-2015 | 150,000 | 525,000.00 | -10.00 | -145.83 |
| 07-Jul-2015 | 150,000 | 487,500.00 | -10.00 | -135.42 |
| 08-Jul-2015 | 150,000 | 487,500.00 | -10.00 | -135.42 |
| 09-Jul-2015 | 150,000 | 450,000.00 | -10.00 | -125.00 |
| 10-Jul-2015 | 150,000 | 450,000.00 | -10.00 | -125.00 |
| 11-Jul-2015 | 150,000 | 450,000.00 | -10.00 | -125.00 |
| 12-Jul-2015 | 150,000 | 450,000.00 | -10.00 | -125.00 |
| 13-Jul-2015 | 150,000 | 450,000.00 | -10.00 | -125.00 |
| 14-Jul-2015 | 150,000 | 487,500.00 | -10.00 | -135.42 |
| 15-Jul-2015 | 150,000 | 487,500.00 | -10.00 | -135.42 |
| 16-Jul-2015 | 150,000 | 487,500.00 | -10.00 | -135.42 |
| 17-Jul-2015 | 150,000 | 450,000.00 | -10.00 | -125.00 |
| 18-Jul-2015 | 150,000 | 450,000.00 | -10.00 | -125.00 |
| 19-Jul-2015 | 150,000 | 450,000.00 | -10.00 | -125.00 |
| 20-Jul-2015 | 150,000 | 450,000.00 | -10.00 | -125.00 |
Ideally the data should look something similar to this:
| Start Date | End Date | # Days | Quantity | Value | Rate | Total Accrual | Avg Daily Accrual |
| 01-Jul-15 | 06-Jul-15 | 5 | 150,000 | 487,500.00 | -10.00 | 677.08 | 135.42 |
| 06-Jul-15 | 07-Jul-15 | 1 | 150,000 | 525,000.00 | -10.00 | 145.83 | 145.83 |
| 07-Jul-15 | 09-Jul-15 | 2 | 150,000 | 487,500.00 | -10.00 | 270.83 | 135.42 |
| 09-Jul-15 | 14-Jul-15 | 5 | 150,000 | 450,000.00 | -10.00 | 625.00 | 125.00 |
| 14-Jul-15 | 17-Jul-15 | 3 | 150,000 | 487,500.00 | -10.00 | 406.25 | 135.42 |
| 17-Jul-15 | 21-Jul-15 | 4 | 150,000 | 450,000.00 | -10.00 | 500.00 | 125.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 Date | End Date | # Days | Quantity | Value | Rate | Total Accrual | Daily Accrual |
| 01-Jul-15 | 17-Jul-15 | 10 | 150,000 | 487,500.00 | -10.00 | 1,354.17 | 135.42 |
| 06-Jul-15 | 07-Jul-15 | 1 | 150,000 | 525,000.00 | -10.00 | 145.83 | 145.83 |
| 09-Jul-15 | 21-Jul-15 | 9 | 150,000 | 450,000.00 | -10.00 | 1,125.00 | 125.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
