Flexible Cumulative within charts

paulm — Tue, 27 Mar 2018 22:26:14 GMT

Hi,

I need advice on if what I am trying to do is even possible - and if it is will it be able to perform in an acceptable fashion.

Dataset:

List of groupings that have transactional values associated with event dates.

Desired outcome:

To produce a report that shows on a given event date what the running total is for each group without showing all event dates associated with values on that given date

Example data:

LOAD * INLINE [
    Group, EventDate, Value
    Test, 25/10/2017, 9822
    Test, 29/10/2017, 20865
    Test, 01/11/2017, 14505
    Test, 05/11/2017, 31321
    Test, 08/11/2017, 16349
    Test, 12/11/2017, 40007
    Test, 15/11/2017, 20541
    Test, 19/11/2017, 45420
    Test, 22/11/2017, 15786
    Test, 26/11/2017, 47207
    Test, 29/11/2017, 25336
    Test, 03/12/2017, 34377
    Test, 06/12/2017, 21313
    Test, 11/12/2017, 56162
    Test, 13/12/2017, 16648
    Test, 17/12/2017, 34222
    Test, 19/12/2017, 21417
    Group2, 16/11/2017, 31321
    Group2, 20/11/2017, 16349
    Group2, 23/11/2017, 40007
    Group2, 27/11/2017, 20541
    Group2, 30/11/2017, 45420
    Group2, 04/12/2017, 15786
    Group2, 07/12/2017, 47207
    Group2, 12/12/2017, 31321
    Group2, 14/12/2017, 16349
    Group2, 18/12/2017, 40007
    Group2, 20/12/2017, 20541
];

What I have done so far:

In the script I have normalised that there is an event on every day for any grouping between its first and last transaction

I have created a running total within the script using peek - but the data is very detailed and I would have to create 20+ of these. To simplify things I have been looking at using set analysis but struggling to it the way I would like. The closest so far is:

=rangesum( above( sum( {$<EventDate={"$(='<='&max(EventDate))"}>} Value),0,RowNo()))

but this only works in a pivot table and will not let me select an event date and see what the total is on that given day.

Help:

Is it possible to create set analysis that can be limited by the event date while showing the values associated with the grouping running totals.

In the example above if I selected the 16/11 I would like to see:

Event Date	Grouping	Daily Value	Running Total
16/11/2017	Test	0	153410
16/11/2017	Group2	31321	31321

Any help greatly appreciated - hopefully I have explained simple enough... it is a lot more complex than this!

topic Flexible Cumulative within charts in QlikView

Flexible Cumulative within charts