# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

## Need help with accumulation formula

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!

Tags (2)
1 Solution

Accepted Solutions
Employee

## Re: Need help with accumulation formula

Hello,

According to your requirement, the following should work:

`RangeSum(Above(Sum(Amount), 0, RowNo()))`

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

4 Replies
Not applicable

## Re: Need help with accumulation formula

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

Not applicable

## Re: Need help with accumulation formula

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
Not applicable

## Re: Need help with accumulation formula

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
SalesRepAmount
A2008Mary100
B2008Joe500
C2009Mary200
D2009Joe400

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

Employee

## Re: Need help with accumulation formula

Hello,

According to your requirement, the following should work:

`RangeSum(Above(Sum(Amount), 0, RowNo()))`

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica