Sum the sum of the previous column value

[afbraga1]

Hello,

I am trying to build a pivot table like the one below but in Qlikview instead of Excel. Currently I am having trouble doing it because of the nature of Initial and Final rows. The Final value is the sum of the Initial from previous month plus Open and Invoiced Orders. This gets me in a loop and I am not realizing how to address it. Has anyone ever done something like this? Thank you in advance!

EDIT: I've attached an example of what i'm trying to do.

EVOLUTION QTY M3	PY	2018.1	2018.2	2018.3	2018.4	2018.5	2018.6	2018.7
Initial	0	42	66	81	95	118,3	125	141
Open Orders	13	0	0	0	0,3	0,7	13	8
Invoiced Orders	29	24	15	14	23	6	3	0
Final	42	66	81	95	118,3	125	141	149

[sunny_talwar]

Would you be able to share the raw data behind this to help you better

[afbraga1]

Thanks for your response Sunny! I've added a example QVW, hopefully it can help portray what I'm trying to do.

[sunny_talwar · Accepted solution]

This?

Initial

=Alt(Before(RangeSum(Before(Sum(#VolumeM3) + Sum(#Invoiced), 0, ColumnNo()))), 0)

Open

sum(#VolumeM3)

Invoiced

sum(#Invoiced)

Final

RangeSum(Sum(#VolumeM3), Sum(#Invoiced), [Initial])

[afbraga1]

Hello Sunny,

Thanks for your response.

I've been testing it and trying to decompose what you've done in order to understand, but could you explain what is the thought process?

Thank you again, amazing!

Best regards,

André Braga

[sunny_talwar]

You are basically accumulating the Sum of Open and Sum of Invoiced and to do that I used the RangeSum(Before()) function.... to get the Initial. Had to use Before... because the first accumulation doesn't happen until the next period...

Does that help? or do you have a more specific question with any of the expressions?

[afbraga1]

The Before() function doesn't actually return a numeric value, does it?... hence you using the RangeSum(), I'm guessing here.

Also I don't understand the use of the ColumnNo().

Best Regards,

André Braga

[sunny_talwar]

Before() does return a numeric value... actually, it returns the value from the previous cell... just for the sake of example... if I do Before(Open)... I will see that the values of Open will shift one cell

ColumnNo() is used for accumulation... it would be good to read about Above()/Below() and After()/Before() here

Missing Manual - Above() and Below()

Missing Manual - Before() and After()