Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey Everyone. I've finally hit a task that I can't find a straight answer on in other threads...
I've got a business requirement to display quartiled results on 10+ metrics in one straight table.
So, the top 25% of performers will be in quartile one (Q1), 26% - 50% will be in Q2, etc...
I've gone down the path of using VALUELIST('Q1','Q2','Q3','Q4') as my dimension,
And:
IF(VALUELIST('Q1','Q2','Q3','Q4') = 'Q1',AVG({<AssociateName={"=RANK(AVG(InCallsHandled),4) < Count(Distinct AssociateName/4"}>} InCallsHandled),
IF(VALUELIST('Q1','Q2','Q3','Q4') = 'Q2',AVG({<AssociateName={"=RANK(AVG(InCallsHandled),4) < (Count(Distinct AssociateName)/2) AND Count(Distinct AssociateName)/4) >= (vQ1)"}>} InCallsHandled),
IF(VALUELIST('Q1','Q2','Q3','Q4') = 'Q3', AVG({<AssociateName={"=RANK(AVG(InCallsHandled),4) < (Count(Distinct AssociateName)*.75) AND RANK(AVG(InCallsHandled),4) >= (Count(Distinct AssociateName)/2)"}>} InCallsHandled),
IF(VALUELIST('Q1','Q2','Q3','Q4') = 'Q4',AVG({<AssociateName={"=RANK(AVG(InCallsHandled),4) >= Count(Distinct AssociateName)*.75"}>} InCallsHandled)))))
as my first measure. However, this only populates the Q4 cell of the table:
Credit: The code above largely came from: This Thread
Any ideas?
Thanks,
JD
Hi , I think you have a missing ')' in couple of places.And check if the variable vQ1 is pre-evaluated value or an expression.You need to change the Q3 expression accordingly
I just modified your expression as follows.
PICK ( WILDMATCH(VALUELIST('Q1','Q2','Q3','Q4'),'Q1','Q2','Q3','Q4') ,
AVG({<AssociateName={"=RANK(AVG(InCallsHandled),4) < Count(Distinct AssociateName)/4"}>} InCallsHandled),
AVG({<AssociateName={"=RANK(AVG(InCallsHandled),4) < (Count(Distinct AssociateName)/2) AND Count(Distinct AssociateName)/4 >= $(vQ1)"}>} InCallsHandled),
AVG({<AssociateName={"=RANK(AVG(InCallsHandled),4) < (Count(Distinct AssociateName)*.75) AND RANK(AVG(InCallsHandled),4) >= (Count(Distinct AssociateName)/2)"}>} InCallsHandled),
AVG({<AssociateName={"=RANK(AVG(InCallsHandled),4) >= Count(Distinct AssociateName)*.75"}>} InCallsHandled)
)
If still getting issue , try to split the expressions inside the set analysis for expressions like
AVG({<AssociateName={"=RANK(AVG(InCallsHandled),4) < (Count(Distinct AssociateName)/2) AND Count(Distinct AssociateName)/4) >= $(vQ1)"}>} InCallsHandled)
change to
AVG({<AssociateName={"=RANK(AVG(InCallsHandled),4) < (Count(Distinct AssociateName)/2)"},AssociateName={"=$(vQ1)<=( Count(Distinct AssociateName)/4 ) "}>} InCallsHandled)
You can try using one of the Quartile expression at a time while debugging to see the issue.
Thanks.
Vikky