Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
kangaroomac
Partner
Partner

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
sunny_talwar

Try this:

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

View solution in original post

4 Replies
sunny_talwar

Try this:

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

View solution in original post

sunny_talwar

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
Partner
Partner
Author

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?

sunny_talwar

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.