how to chart accumulation not affected by selection

[Anonymous]

Hello there,

I'm working on a chart showing the yearly accumulative count on selected products.

For example here, the chart is showing the accumulative count for product TXZ600 from its year 2014 to 2017.

The accumulative count is 33 up to year 2017.

However, if I select 2017 only, The chart is only showing the count of 4 for 2017.

How to make the chart showing the accumulative count only based on the selection of product, not year?

Thank you in advance for helping out.

Josh

[swuehl]

Using a recent QV version, you could try using the sortable aggr() function:

Only(aggr(Rangesum(Above(count({1}Distinct SN),0,RowNo())),product,(shpDateYY,(NUMERIC,ASCENDING))))

See also

Calculating rolling n-period totals, averages or other aggregations

The sortable Aggr function is finally here!

[Anonymous]

Hello Stefan,

The expression helps but with few problems.

1. the version of QV I'm using doesn't recognize (shpDateYY,(NUMERIC,ASCENDING))

2. If I used

Only(aggr(Rangesum(Above(count({1}Distinct SN),0,RowNo())),product,shpDateYY))

and no product selected, chart shows no data

3. if a product is selected, the count doesn't match with the chart which has expression sum(aggr(count({1} Distinct SN),product,shpDateYY)) and "Full Accumulation" checked.

Thanks,

Josh

[swuehl · Accepted solution]

1) The syntax for the sortable aggr() dimensions is only available in recent QV12 releases.

2) You need to use another outer aggregation function, Sum() instead of Only() if you want to aggregate all possible products.

3) If the sortable aggr() dimension syntax does not work, Qlik will bay default sort the dimension values (shpYear) in LOAD order, and in your case, LOAD order for shpYear comes in 2014,2016,2015,2017

That's why the accumulated values using the rangesum(above()) doesn't give the correct results.

You can fix it by creating a correct LOAD order in the script (using e.g. a shpYear field loaded first in the script with the correct chronological order).

Alternatively, use an asof-table approach:

The As-Of Table

I've attached both approaches in the updated QVW.