Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmark1990
Contributor III
Contributor III

How can I add values from the last row in 3 others columns to the value of first column

I currently have a straight table with (dummy) financial data. It has the dimension JaarWeek and 4 expressions:2018-12-13_1217.png

The start value of Saldo liquide middelen bij (liquid assets) is 30.0. What I need to do is starting at 30.0 and add all of the values of the three other columns to it and put the new value for liquid assets below the first value. Then add the three values in that row to liquid values and so on. To clearify, I hardcoded the table for the correct output of the calculation:2018-12-13_1217_001.pngWhen I use the expression =NUM#($(startsaldoLiquideMiddelen), '0.0') + (NUM#([Verwachte betalingen debiteuren Af:], '0.0')) + (NUM#([Geplande betalingen crediteuren], '0.0')) - (NUM#([BTW-afdracht Belastingdienst:], '0.0')) for Saldo liquide middelen bij, I get the following incorrect table:2018-12-13_1347.png

I also tried to use the ABOVE() function to add the values of the row above the current row. In the first case this would be null and keep liquid assets on 30 for the first row. My ABOVE expression is:

=NUM#($(startsaldoLiquideMiddelen), '0.0') + ABOVE(NUM#([Verwachte betalingen debiteuren Af:], '0.0')) + ABOVE(NUM#([Geplande betalingen crediteuren], '0.0')) - ABOVE(NUM#([BTW-afdracht Belastingdienst:], '0.0')) and this results in the first table as posted.

How can I perform this calculation correctly? I don't want to do this in the script but in the straight table itself.

The variable $(startsaldoLiquideMiddelen) is declared as SET startsaldoLiquideMiddelen = 30

Labels (2)
3 Replies
sunny_talwar

Since the data is dummy in this app, would you be able to share it with us so that we can see and try out few things?
qlikmark1990
Contributor III
Contributor III
Author

I extracted the dummy data and put in a new document. Now I note that the table has only null values in the most left column.

I have a personal edition of QLikView so I can't open files from others.

sunny_talwar

How are you getting -90 for 2018-43? I got this

image.png

Expression I used

=RangeSum(
Num#($(startsaldoLiquideMiddelen), '##.0', '.', ','),
Above(Num#([Verwachte betalingen debiteuren Af:], '##.0', '.', ',')),
Above(Num#([Geplande betalingen crediteuren], '##.0', '.', ',')),
-Above(Num#([BTW-afdracht Belastingdienst:], '##.0', '.', ',')))