topic Re: Script Level Calculation Workaround in QlikView

Script Level Calculation Workaround

aslam24196 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Script Level Calculation Workaround

YoussefBelloum — Mon, 22 Jan 2018 09:14:43 GMT

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

Re: Script Level Calculation Workaround

swuehl — Mon, 22 Jan 2018 09:25:21 GMT

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

Re: Script Level Calculation Workaround

aslam24196 — Mon, 22 Jan 2018 11:56:53 GMT

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.

Re: Script Level Calculation Workaround

swuehl — Mon, 22 Jan 2018 12:01:11 GMT

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.

Re: Script Level Calculation Workaround

aslam24196 — Mon, 22 Jan 2018 12:23:37 GMT

Thank You Stefan

Very Helpful.