Running totals per dimension (RangeSum(), sortable Aggr()...)

    Inspired by a user thread (Issues with Full Accumulation) I like to explain how to calculate running totals within a separate dimension with the help of chart expressions. (I know that As-Of Tables also provide an alternative... however not in this article ). I contributed something similar yet (Using inter-record functions: swap x-axis and legend in charts) but the follwing approach is more general.

     

    Using QlikView 12 is mandatory, because the sortable aggr() function was introduced with QlikView 12.

    Input shall be an Excel file:

     

    Lets suppose we need 2 dimension for our chart, then for these 2 dimension, each combination (cartesian product) must exist. (Building input data like that shall not be scope of this tutorial.)

     

    I called them "cartesian" dimensions:

     

    • Datefield_cartesian_product
    • Color_cartesian_product

     

    Basically I like to show how you can calculate running totals per "Datefield" for each "Color" separately

    with the help of RangeSum() and to sortable aggr() function.

     

    Example 1: running totals Color per Datefield


    RangeSum(Above(count(Color),0,RowNo()))

     

    The data is sorted in the following manner:

    Everything looks fine. For each"Color" we see the accumulated values per "Datefield". This is nothing special.

     

    Example 2: running totals Datefield per Color


    Now lets just change the sorting of the table and we're ready, aren't we?

    The changed dimension order leads to different RowNo() values leads to totally different results.

    The data is sorted in the following manner:

    Now we use the the new Aggr() function that supports dimension sorting.

    You see that the dimensions are sorted in exactely the same way as the were sorted in Example 1:

    (first the color, then the datefield)

     

    aggr(RangeSum(Above(count(Color),0,RowNo())),
    (
    Color_cartesian_product,(TEXT,ASCENDING)),
    (Datefield_cartesian_product,(NUMERIC,ASCENDING)))

     

    and so the result of the expression is also the same as in Example 1 although the chart itself is sorted differently:

    If you know another solution just with the help of chart functions please let me know.

     

    Regards Robin