Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Full running total in a pivot table

Hi everyone,

I hop if someone can help me with this.

I have a table that looks like this (this is a simple version):

Test:
LOAD * INLINE [
     Jaar, Maand, Waarde
     2014, jan, 1
     2014, feb, 1
     2014, mrt, 1
     2014, apr, 1
     2014, mei, 1
     2014, jun, 1
     2014, jul, 1
     2014, aug, 1
     2014, sep, 1
     2014, okt, 1
     2014, nov, 1
     2014, dec, 1
     2013, jan, 1
     2013, feb, 1
     2013, mrt, 1
     2013, apr, 1
     2013, mei, 1
     2013, jun, 1
     2013, jul, 1
     2013, aug, 1
     2013, sep, 1
     2013, okt, 1
     2013, nov, 1
     2013, dec, 1
     2015, jan, 1
     2015, feb, 1
     2015, mrt, 1
     2015, apr, 1
     2015, mei, 1
];

I want to have a pivot table with a full running total that look like this:

Result.PNG


I've been trying all kinds of solutions but none of them works.

The result that i have now is , is the following:

 

result now.PNG

The formula i am using now for this is :

rangesum ( sum (Waarde) , Before ( total  sum (Waarde), 1, ColumnNo(TOTAL) ) )


This works (also other constructions) just for one year. But a new row starts the running total from the beginning and it needs a full running total.

I hope someone has the solution for me.


Grtz, Mario

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Just add a {1} set identifier to the inner aggregation:

=sum(aggr( rangesum(above(TOTAL sum({1}Waarde),0,rowno(TOTAL))), Jaar, Maand))

View solution in original post

4 Replies
swuehl
MVP
MVP

Maybe like attached?

Or would it be possible to calculate the running total in the script?

Not applicable
Author

Hi swuehl,

Thanks for your response. It's getting close but it is not exactly what i'm looking for.

Maybe i should have mentioned it in my first message but when i make a selection in Year or Month the values have to be exactly the same. Example, if i select 2014 i have one row. It should start in january with 13, february 14 etc.

In the original QVW there are more dimensions that the user can make selections in. These selections can not be ignored. So if i make a selection 2014 and March and April it needs to display:

Result1.PNG

I think that the best way is to solve this in the load script. But i'm hoping there is a more simple solution to do it in the front end.

Grtz.

swuehl
MVP
MVP

Just add a {1} set identifier to the inner aggregation:

=sum(aggr( rangesum(above(TOTAL sum({1}Waarde),0,rowno(TOTAL))), Jaar, Maand))

Not applicable
Author

Thanks swuehl,

This looks like it the solution that i need.

Tomorrow i'm going the implemented it in the original qvw.

Grtz.