Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kangaroomac
Contributor II

Accumultation / running totals

Good Day,

I'm having a head scratcher with regards to accumulation.

It is working when no selections on i.e. Year is made.

YearAmountAccumulation
2010100100
2011200300
2012300600
20134001000
20145001500
20156002100
20167002800

The goal is to have a selection made on Year (i.e. 2015 and 2016) and have it with the same accumulation / running total figures as above.

The Accumulation expression so far:

RangeSum(Above(Total

Sum(

     {<

     >}

         Amount)

, 0, RowNo(Total)))

Unfortunately doing the logic in the script is not an option (the real solution enables users to make various filter selections).

I have attached a sample QVF.

Example of required result:

Selection made: 2015, 2016

Desired result:

YearAccumulation
20152100
20162800
1 Solution

Accepted Solutions
MVP
MVP

Re: Accumultation / running totals

Try this:

RangeSum(Above(Total Sum({<Year>} Amount), 0, RowNo(Total))) * Avg(1)

4 Replies
MVP
MVP

Re: Accumultation / running totals

Try this:

RangeSum(Above(Total Sum({<Year>} Amount), 0, RowNo(Total))) * Avg(1)

MVP
MVP

Re: Accumultation / running totals

In addition since Year is the only dimension, you might not even need a TOTAL qualifier here.

RangeSum(Above(Sum({<Year>} Amount), 0, RowNo())) * Avg(1)

kangaroomac
Contributor II

Re: Accumultation / running totals

Hi Sunny,

Thanks, that did the trick.

Your additions are Year and * Avg(1).

I understand Year (same as Year = i.e. ignore any selections made on Year)

The * Avg(1) works, but why?

MVP
MVP

Re: Accumultation / running totals

Adding Avg(1) would be 1 for all the years which are selected and for non-selected years Avg(1) will be 0. So this is an alternative of using an if statement which allow me to make those years 0 which are not selected.