Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Just add a {1} set identifier to the inner aggregation:
=sum(aggr( rangesum(above(TOTAL sum({1}Waarde),0,rowno(TOTAL))), Jaar, Maand))
Maybe like attached?
Or would it be possible to calculate the running total in the script?
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.
Just add a {1} set identifier to the inner aggregation:
=sum(aggr( rangesum(above(TOTAL sum({1}Waarde),0,rowno(TOTAL))), Jaar, Maand))
Thanks swuehl,
This looks like it the solution that i need.
Tomorrow i'm going the implemented it in the original qvw.
Grtz.