Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cumulative Averages

How do I get a value for the cumulative value of any given ratio? I am computing Manufacturing Profit Margin % = Manufacturing Profit / Total Sales. I am trying to write an expression that will sum compute the overall value by taking Manufacturing Profit 1 + Manufacturing Profit 2 +Manufacturing Profit 3 / Total Sales 1 + Total Sales 2 + Total Sales 3 = Cumulative Manufacturing Profit Margin % for periods 1-3.  Any help?

20 Replies
sunny_talwar

Check this out:

Capture.PNG

Script:

Table:

LOAD * Inline [

Month, Metric, Value

1, Metric 1, 23996

1, Metric 2, 6245

2, Metric 1, 47980

2, Metric 2, 12489

3, Metric 1, 33692

3, Metric 2, 22108

];

Expression1: =Sum({<Metric = {'Metric 1'}>}Value)

Expression2: =Sum({<Metric = {'Metric 2'}>}Value)

Expression3: =Sum({<Metric = {'Metric 2'}>}Value)/Sum({<Metric = {'Metric 1'}>}Value)

Does this help?

Not applicable
Author

Still no luck, Im getting the following, when I want to display the following values. I'm importing the numerator and denominator separately, summing them each month, and dividing the cumulative sum each month to mark the YTD margin %.

   

0.260252
0.260296
0.65618
0.480691
1.482634
0.863829
2.405509
1.462208
2.981861
1.634095
3.671661

2.017935

exampels.pngExample: Desired End Data and Current Chart

sunny_talwar

Can you give me non cumulative data so that i can do it from scratch for you Michael?

Best,

Sunny

Not applicable
Author

See attached file

Not applicable
Author

Any luck?

sunny_talwar

Going to look at it know ... Sorry got busy with some meetings

sunny_talwar

Can you verify that is this what you are trying to do?

Not applicable
Author

Yes

sunny_talwar

Hopefully this:

Capture.PNG

Not applicable
Author

Thank you so much, I was so close. RangeSum(Above(Sum(), 0, RowNo())) was very helpful.