topic Re: Calculated Expression Use as a Dimension in QlikView

Calculated Expression Use as a Dimension

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

Hi Community,

I made some calculated expression in order to calculate the scorecard for sale hierarchy,

after calculating the score, I assign a grade against each sale representative(Expression Given below) whichi achieved successfully.

But i also want a summary of assigning grades like

Count of Excellent is 50 let suppose

Count of Satisfactory =20

and so on....

How can i do that?

if (

( [Total BSC Score] )

>= 90 , 'Excellent'

,

if (

([Total BSC Score])

>= '80' and ([Total BSC Score] ) < '90' , 'Satisfactory'

,

if (

([Total BSC Score] )

>= '70' and ([Total BSC Score] ) < '80' , 'Average' ,

if (

([Total BSC Score] )

< '70' , 'Under Performing' ) )

)

)

Re: Calculated Expression Use as a Dimension

shiveshsingh — Fri, 22 Feb 2019 07:23:06 GMT

Can you share some sample to work upon?

This can be achieved using bucket logic in calculated Dimension.

Re: Calculated Expression Use as a Dimension

noman212 — Fri, 22 Feb 2019 07:51:19 GMT

Please refer the snapshot, sample work is not available.

I calculate percentages and according to their percentage slab, I calculate the score of the individuals.

combining the score from all parameter and taking the sum of all expression's label like [A] +[B] + [C] = Total score then i calculate the Grade like Excellent, underperforming etc.

i also create variables for that in order to use Grade as a Dimension and expression as the count of the salesperson.

Re: Calculated Expression Use as a Dimension

ferryjong — Fri, 22 Feb 2019 08:43:20 GMT

Hi,

You can use Set Analysis to count, for example, Excellent. You have to use this syntax:

Count({$<[Total BSC Score] = {"=>90"}>} [Total BSC Score])

For the others you can change the syntax. For example, use {">=70 < 80"} to count 'Average'.

Re: Calculated Expression Use as a Dimension

noman212 — Fri, 22 Feb 2019 10:24:11 GMT

not working.

Re: Calculated Expression Use as a Dimension

noman212 — Fri, 22 Feb 2019 10:25:45 GMT

Anyone else?

Please come up with solutions.

Re: Calculated Expression Use as a Dimension

sunny_talwar — Fri, 22 Feb 2019 13:45:31 GMT

Is [Total BSC Score] a field created in the script? If it is, why don't you just perform this in the script like this?

If([Total BSC Score] >= 90, 'Excellent',
If([Total BSC Score] >= '80', 'Satisfactory',
If([Total BSC Score] >= '70', 'Average', 'Under Performing'))) as Ratings

and now do this

Dimension

Ratings

Expression

Count(Ratings)

Re: Calculated Expression Use as a Dimension

noman212 — Mon, 25 Feb 2019 11:02:36 GMT

Hi Sunny,
Thanks for replying.
[Total BSC Score] is a calculated expression, which is the sum of 5 expressions.
I also made variables for these expressions, but the question is how can I put the final variable in dimension?

Re: Calculated Expression Use as a Dimension

noman212 — Tue, 26 Feb 2019 10:07:11 GMT

Thanks, Shiveshsingh.
I implement your suggestion and its working.
Aggr(
If([Total Score] >= 90, Dual('Excellent', 1),
If([Total Score] >= '80' and ([Total Score]) < '90' , Dual('Satisfactory', 2),
If([Total Score] >= '70' and (TotalBSCScore) < '80' , Dual('Average', 3),
If([Total Score] < '70' , Dual('Under Performing', 4))))),DIM1 , DIM2)