Quartiling Multiple Measures in one Table

[jldavis22]

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

[vikramv]

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