Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik-Experts!
I need help with a certain accumulation formula.
I have to accumulate booking amounts over the years in a pivot table. With some search in the community I found a formula which works when the last dimension in the pivot table is the booking year, but not when other dimensions are placed after the year.
Please find a simplified sample of my problem attached. The formula I am using right now is:
= if(rowno(),rangesum(sum(Amount ) , above(sum(Amount) ,1,rowno())),sum( Amount ) )
And I have problems to adapt it in a way, that it is somehow fixed to the year dimension.
Any help is much appreciated!
Hello,
According to your requirement, the following should work:
RangeSum(Above(Sum(Amount), 0, RowNo()))
BI Consultant
Hi,
aggregate the whole expression per Year field:
=aggr( if(rowno(),rangesum(sum(Amount ) , above( sum(Amount) ,1,rowno())),sum( Amount ) ) ,Year)
Hope that helps!
Stefan
Okay, works so far for aggregation over the years no matter which dimension is else in the pivot table.
Thank you so far!
Now I figured out I need to change the description of the requirement a bit.
I need to aggregate over the years but only for the dimension which come after the year.
So if the original data looks like this:
%Key1 | Year | SalesRep | Amount |
A | 2008 | Mary | 100 |
A | 2009 | Mary | 200 |
B | 2008 | Joe | 500 |
B | 2009 | Joe | 400 |
I would need the output like this (accumulated over the year but still a drilldown through the dimensions in the pivot)
Year SalesRep Amount
2008 Mary 100
2009 Mary 300
2008 Joe 500
2009 Joe 900
The expression from Steve gives me:
Year | SalesRep | Amount |
2008 | Mary | 600 |
2009 | Mary | 1200 |
Hi,
i was unable to make the requested chart wthout modify the data a bit. The change is that every row has unique %Key1 field based on Year&SalesRep combination. The modify table look like this bolow:
%Key1 | Year | SalesRep | Amount |
---|---|---|---|
A | 2008 | Mary | 100 |
B | 2008 | Joe | 500 |
C | 2009 | Mary | 200 |
D | 2009 | Joe | 400 |
And the expression is:
aggr( rangesum( sum(Amount), above(sum(Amount),1,rowno())) ,%Key1)
Also the sort of the SalesRep must be on %Key1 field.
With this changes the result table is:
If no unique key is availabe in the datasource you can always use the Autonumber number function to make unique key based on a field combination.
Hope this helps!
Stefan
Hello,
According to your requirement, the following should work:
RangeSum(Above(Sum(Amount), 0, RowNo()))
BI Consultant