topic Re: Accumulation in Qlikview in QlikView

Accumulation in Qlikview

Mon, 08 Jun 2015 10:00:30 GMT

Hi Experts,

As per my requirement i need to do an accumulation as explained below.Which is kind of a negative accumulation.

The first value on the second column is sum of the values in the first column.And then the values are getting subtracted.

Data	After Accumulation
1100	2400
200	2200
400	1800
200	1600
500	1100

Any help would be highly appreciated.

Re: Accumulation in Qlikview

swuehl — Mon, 08 Jun 2015 10:02:47 GMT

Maybe something like

=sum(TOTAL Data) - rangesum(above( sum(Data),0,rowno() ))

edit:

Or, to exclude the very first line from the rangesum, maybe

sum(TOTAL Data) - rangesum(above( sum(Data),0,rowno()-1 ))

Re: Accumulation in Qlikview

Kushal_Chawda — Mon, 08 Jun 2015 10:08:22 GMT

Can you share the data with more information

Re: Accumulation in Qlikview

hariprasadqv — Mon, 08 Jun 2015 10:52:15 GMT

I chime with Swuehl reply.

Re: Accumulation in Qlikview

MayilVahanan — Mon, 08 Jun 2015 10:58:06 GMT

Try like this

Sum(TOTAL Datas)- rangesum(above( sum(Datas),0,rowno()-1 ))

Re: Accumulation in Qlikview

petter — Mon, 08 Jun 2015 13:48:45 GMT

From the test data you provide there must be an opening balance which seems to be the number 2400 in the top. So I took this as an opening balance and then the rest is just a matter of subtracting - calculating all the current balances for each row. The expression I would suggest looks like this:

Add a row ID in your load script and then create a Straight Table with an expression.

This expression (column ) should have the label Balance - hence the reference to [Balance] in the expression:

If( IsNull( Above( [Balance] ) ) , 2400 , Above( [Balance] ) - [Data] )

The IsNull(Above([Balance])), 2400 is to establish the opening balance - what follows is the calculation for each row afterwards.

NOTES ON PERFORMANCE:

I started out with a different approach than most of what was suggested here. But when I was about to post I saw the other solution by swuehl which is absolutely valid although with the slight fix that Mayil suggested.

My suggestion however avoided the use of Above which recalculate for each row the total sum of all rows. I found that very calculation intensive so I went for a simple sum/substraction. But then I got curious how they would compare in actual usage. Would QlikView be able to be smart so the calculations were performing equally well... I made a more comprehensive test-data to be able to measure the difference. And my simple test absolutely shows that recalculation.

So using the expression I suggest and having a table of 10000 rows it will calculate in 120 ms on my laptop. Using the expression which use the Above( ... , ... , ....) three parameter version it use 20000 ms for the same calculation. You might say that since you only have a few rows in the calculation you might not notice a difference. And that is correct unless you make it into a habit of using calculation intensive functions without searching for a less calculation intensive calculation. In the end you will end up with a lot of applications that consume a lot of CPU - possibly orders of magnitude more than they need to.

I have seen that happen too many times to not point out the culprit when I encounter it. What might work quickly might end up not scaling at all - have a close look at your expressions before going into production and you might need a lot of data being calculated that it all matters how you construct your expressions.

Re: Accumulation in Qlikview

ramoncova06 — Mon, 08 Jun 2015 14:38:59 GMT

you can use the below function for this

rangesum(sum(Data),Below([After Accumulation]))

see the attached example