Need help with grouping like data that has sequential gaps

Tue, 18 Aug 2015 20:47:56 GMT

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

Re: Need help with grouping like data that has sequential gaps

Gysbert_Wassenaar — Wed, 19 Aug 2015 11:54:56 GMT

See attached qvw.

Re: Need help with grouping like data that has sequential gaps

Wed, 19 Aug 2015 14:08:23 GMT

Awesome - thank you for the prompt response.

topic Re: Need help with grouping like data that has sequential gaps in QlikView

Need help with grouping like data that has sequential gaps

Re: Need help with grouping like data that has sequential gaps

Re: Need help with grouping like data that has sequential gaps