topic Re: Full running total in a pivot table in QlikView

Full running total in a pivot table

Mon, 08 Jun 2015 16:10:02 GMT

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:

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

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

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

Re: Full running total in a pivot table

swuehl — Mon, 08 Jun 2015 16:43:08 GMT

Maybe like attached?

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

Re: Full running total in a pivot table

Mon, 08 Jun 2015 17:01:10 GMT

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:

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.

Re: Full running total in a pivot table

swuehl — Mon, 08 Jun 2015 17:14:26 GMT

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

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

Re: Full running total in a pivot table

Mon, 08 Jun 2015 17:43:49 GMT

Thanks swuehl,

This looks like it the solution that i need.

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

Grtz.