Calculated Expression Use as a Dimension

[noman212]

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

)



)

[shiveshsingh · Accepted solution]

Can you share some sample to work upon?

This can be achieved using bucket logic in calculated Dimension.

[noman212]

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.

[ferryjong]

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'.

[noman212]

not working.

[noman212]

Anyone else?

Please come up with solutions.

[sunny_talwar]

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)

[noman212]

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?

[noman212 · Accepted solution]

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)