9 Replies Latest reply: Jan 12, 2017 1:54 AM by Panwipa Wattthanapranwong

# How to do: calculate %rank & count people

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
• ###### Re: How to do: calculate %rank & count people

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

• ###### Re: How to do: calculate %rank & count people

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

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

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)

• ###### Re: How to do: calculate %rank & count people

Thank you very much.

• ###### Re: How to do: calculate %rank & count people

Hello Sunny T,

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

• ###### Re: How to do: calculate %rank & count people

May be like this

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

• ###### Re: How to do: calculate %rank & count people

Thank you so much. You are my Hero.

• ###### Re: How to do: calculate %rank & count people

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

Amount/Target AS Percent

Expressions:

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

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

See attached example:

• ###### Re: How to do: calculate %rank & count people

Thank you very much.