Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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:
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)
Thank you very much.
Thank you very much.
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
May be like this
Count(DISTINCT {<Name = {"=Sum(Amount)/Sum(Target) >= 0.7 and Sum(Amount)/Sum(Target) <= 0.79"}>} Name)
Please mark the thread as answered.
Thank you so much. You are my Hero.