Script Level Calculation Workaround

[aslam24196]

Hi,

I am stuck at a script level calculation

Consider a load statement,

LOAD Continent,

           Country,

           [# of Managers],

           [# of Non Managers],

           [# of Non Mangers]/ [# of Managers] as [Ratio of Non Managers and Managers]

FROM source;

On the UI level if I create a Straight Table With Country and Sum(Ratio) it gives me the desired output.

But when it is a Straight Chart with Continent and Sum(Ratio) it gives me the sum of all the ratio values of the countries withing the continent.

Example:

Country     Manager     Non Manager Ratio

----------------------------------------------------------

A                    8                    16               2

B                    4                    12               3

Continent     Manager     Non Manager     Ratio

------------------------------------------------------------------

AB                   12                  28               Here i get 5 instead of 2.33

I want the workaround for giving me the ratio of # of Managers and # Non Managers for Both Country and Continent and not the Sum of Ratio.

[YoussefBelloum]

Hi,

Maybe you should divide the Ratio by the number of the countries, like this:

=sum(Ratio)/Count(DISTINCT Country)

and maybe do the same for the continent dimension

[swuehl]

Your ratio is a non-additive measure.

Additive and Non-Additive Numbers

Keep the original people count in your data and calculate the ratio in your chart expression

=Sum(  [# of Managers]) / Sum( [# of Non Managers] )

[aslam24196]

Hey Stefan,

Your feedback is helpful but I am unsure of how to proceed in my Situation as you see in the attached image, I have used my Expressions as Dimensions and Using Set analysis have calculated the # of Managers, #Non Managers and Ratio for each Quarter. This was the reason I used the Calculation at Script Level.

Can I add a Calculated value to the KPI List?

Or Is there any other way for this?

The Above is for a Continent where for each quarter the Ratio is actually the sum of the ratios for the Countries within the Continent.

[swuehl · Accepted solution]

You can use a ValueList() in your dimension and expression or a data island table to create the KPI dimension.

Then use a Pick(Match() ) combination to branch your expression into the appropriate expression calculation.

[aslam24196]

Thank You Stefan

Very Helpful.