Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Sum the sum of the previous column value

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 M3PY2018.12018.22018.32018.42018.52018.62018.7
Initial042668195118,3125141
Open Orders130000,30,7138
Invoiced Orders2924151423630
Final42668195118,3125141149
1 Solution

Accepted Solutions
Highlighted

This?

Capture.PNG

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

View solution in original post

7 Replies
Highlighted

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

Highlighted
Creator
Creator

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

Highlighted

This?

Capture.PNG

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

View solution in original post

Highlighted
Creator
Creator

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

Highlighted

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?

Highlighted
Creator
Creator

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

Highlighted

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

Capture.PNG

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