Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
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
Highlighted
MVP
MVP

Re: Full running total in a pivot table

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
Highlighted
MVP
MVP

Re: Full running total in a pivot table

Maybe like attached?

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

Highlighted
Not applicable

Re: Full running total in a pivot table

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.

Highlighted
MVP
MVP

Re: Full running total in a pivot table

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

Highlighted
Not applicable

Re: Full running total in a pivot table

Thanks swuehl,

This looks like it the solution that i need.

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

Grtz.