How to do: calculate %rank & count people

[panwipa2528]

Data

Name	Tier	Target	Amount
Derek	A	100	70
Malee	B	1,000	100
Joe	B	1,000	400
Gill	A	200	150
Mike	A	100	10

Result

Calculate % from amount/target and then count person by tier & rank as below

Tier	>=50%	<50%
A	2	1
B	0	2

[lironbaram]

hi

build a table with this field

Tier as dimension

>=50% - sum(aggr(if(sum(Amount)/sum(Target)>=0.5,1,0),Name,Tier))

<50% - sum(aggr(if(sum(Amount)/sum(Target)<0.5,1,0),Name,Tier))

attach is a demo app

[Anonymous]

What kind of chart object do you plan on using? The solutions could vary depending on the chart object but let's assume it's a straight table, you could create a new percent field in the load script and then use set analysis on the front end to count the people as shown below;

Step 1: Create new percent field

Load Script;

Amount/Target AS Percent

Step 2: Add measures

Expressions:

>= 50%: Count({$<Percent={">=0.5"}>}Name)

<50%: Count({$<Percent={"<0.5"}>}Name)

See attached example:

[sunny_talwar]

Using lironbaram‌'s (thank you) sample, you can also try this:

>=50% - Count(DISTINCT {<Name = {"=Sum(Amount)/Sum(Target) >= 0.5"}>} Name)

<50% - Count(DISTINCT {<Name = {"=Sum(Amount)/Sum(Target) < 0.5"}>} Name)

This will help you avoid the use of Aggr() function.

NOTE: This may not work if you have same name in both the Tier. If that happens, I would go back to Liron's solution or using a script based workaround where you can create a new field where you combine Name&Tier

LOAD Name,

           Tier,

           Name&Tier as NewField,

           ....

FROM ....;

and then you can use something like this:

>=50% - Count(DISTINCT {<NewField = {"=Sum(Amount)/Sum(Target) >= 0.5"}>} Name)

<50% - Count(DISTINCT {<NewField = {"=Sum(Amount)/Sum(Target) < 0.5"}>} Name)

[panwipa2528]

Thank you very much.

[panwipa2528]

Thank you very much.

[panwipa2528]

Hello Sunny T,

Need your help again.

If I have rank 70-79%, how do i do?
I use--> Count(DISTINCT {<Name = {"=Sum(Amount)/Sum(Target) >= 0.5"}>} Name)

Best Regards,

Film

[sunny_talwar]

May be like this

Count(DISTINCT {<Name = {"=Sum(Amount)/Sum(Target) >= 0.7 and Sum(Amount)/Sum(Target) <= 0.79"}>} Name)

[parimikittu]

Please mark the thread as answered.

[panwipa2528]

Thank you so much. You are my Hero.