topic Re: Cumulative Averages in QlikView

Cumulative Averages

Tue, 07 Jul 2015 12:58:36 GMT

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?

Re: Cumulative Averages

sunny_talwar — Tue, 07 Jul 2015 13:00:07 GMT

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

Best,

Sunny

Re: Cumulative Averages

Tue, 07 Jul 2015 13:01:18 GMT

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

Re: Cumulative Averages

sunny_talwar — Tue, 07 Jul 2015 13:02:14 GMT

Try this:

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

Re: Cumulative Averages

sunny_talwar — Tue, 07 Jul 2015 13:03:38 GMT

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

Re: Cumulative Averages

Tue, 07 Jul 2015 14:08:32 GMT

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

Re: Cumulative Averages

sunny_talwar — Tue, 07 Jul 2015 14:38:05 GMT

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

Re: Cumulative Averages

Tue, 07 Jul 2015 14:40:11 GMT

Here is a better example:

Month	1	2	3	Total
Metric 2	23996	47980	33692	105668
Metric 1	6245	12489	22108	40842
Margin	0.260252	0.260296	0.65618	0.386512	This is what I want

Re: Cumulative Averages

Tue, 07 Jul 2015 14:52:53 GMT

Any idea?

Re: Cumulative Averages

sunny_talwar — Tue, 07 Jul 2015 15:00:16 GMT

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

Best,

Sunny

Re: Cumulative Averages

sunny_talwar — Tue, 07 Jul 2015 15:25:23 GMT

Check this out:

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?

Re: Cumulative Averages

Tue, 07 Jul 2015 15:36:15 GMT

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

Example: Desired End Data and Current Chart

Re: Cumulative Averages

sunny_talwar — Tue, 07 Jul 2015 16:00:35 GMT

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

Best,

Sunny

Re: Cumulative Averages

Tue, 07 Jul 2015 16:19:51 GMT

See attached file

Re: Cumulative Averages

Tue, 07 Jul 2015 16:48:45 GMT

Any luck?

Re: Cumulative Averages

sunny_talwar — Tue, 07 Jul 2015 16:53:59 GMT

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

Re: Cumulative Averages

sunny_talwar — Tue, 07 Jul 2015 16:56:09 GMT

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

Re: Cumulative Averages

Tue, 07 Jul 2015 17:02:44 GMT

Yes

Re: Cumulative Averages

sunny_talwar — Tue, 07 Jul 2015 17:11:54 GMT

Hopefully this:

Re: Cumulative Averages

Tue, 07 Jul 2015 17:19:17 GMT

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