Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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])
Would you be able to share the raw data behind this to help you better
Thanks for your response Sunny! I've added a example QVW, hopefully it can help portray what I'm trying to do.
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])
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
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?
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
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