Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Accumulation in Qlikview

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.

  

DataAfter Accumulation
11002400
2002200
4001800
2001600
5001100

Any help would be highly appreciated.

6 Replies
MVP
MVP

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 ))

MVP
MVP

Can you share the data with more information

Creator III
Creator III

I chime with Swuehl reply.

HI

Try like this

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

Thanks & Regards,
Mayil Vahanan R
MVP
MVP

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.

Specialist III
Specialist III

you can use the below function for this

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

see the attached example