Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

Re: Cumulative Averages

Hopefully this:

Capture.PNG

20 Replies
sunny_talwar
Not applicable

Re: Cumulative Averages

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

Best,

Sunny

Not applicable

Re: Cumulative Averages

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

sunny_talwar
Not applicable

Re: Cumulative Averages

Try this:

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

sunny_talwar
Not applicable

Re: Cumulative Averages

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

Re: Cumulative Averages

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
Not applicable

Re: Cumulative Averages

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

Re: Cumulative Averages

Here is a better example:

   

   

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

Re: Cumulative Averages

Any idea?

sunny_talwar
Not applicable

Re: Cumulative Averages

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

Best,

Sunny