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?

1 Solution

Accepted Solutions
sunny_talwar

20 Replies
sunny_talwar

Not sure I completely understand, do you have sample data or app that we can look at?

Best,

Sunny

Not applicable
Author

I simply want to cumulate the numerator and denominator before dividing to get the overall values of each.

sunny_talwar

Try this:

RangeSum(Manufacturing Profit 1 + Manufacturing Profit 2 +Manufacturing Profit 3)/RangeSum(Total Sales 1 + Total Sales 2 + Total Sales 3)

sunny_talwar

Or simply do this (but this might give you problems when you have nulls). RangeSum would be a better solution I believe.

(Manufacturing Profit 1 + Manufacturing Profit 2 +Manufacturing Profit 3)/(Total Sales 1 + Total Sales 2 + Total Sales 3)

Best,

Sunny

Not applicable
Author

No that didn't work, here is what I have thus far, and it is still not calculating. I am using full accumulation, and will need to calculate my cumulative margin similar to what's attached in Excel.

=Sum({$<[Finance Data.Metric]={'Manufacturing Profit'}>} [Finance Data.Act/Fcst])
/
Sum({$<[Finance Data.Metric]={'Total Sales'}>} [Finance Data.Act/Fcst])

sunny_talwar

The file you attached is already accumulating Metric 1 and Metric 2? I wouldn't be able to help you if you don't give me a sample from raw data. I am not sure what exactly are you doing for accumulation. I hope that make sense.


Best,

Sunny

Not applicable
Author

Here is a better example:

   

   

Month 123Total
Metric 2239964798033692105668
Metric 16245124892210840842
Margin0.2602520.2602960.656180.386512This is what I want
Not applicable
Author

Any idea?

sunny_talwar

Give me sometime... checking it in a bit.

Best,

Sunny