Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
paulm
Contributor III
Contributor III

Flexible Cumulative within charts

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 DateGroupingDaily ValueRunning Total
16/11/2017Test0153410
16/11/2017Group23132131321

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

0 Replies