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:
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!
