Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can someone explain what's happening here? I have this formula that is supposed to get the sum number of sales for each employee ID in each year month. Then it's supposed to compare it to these different fractiles and give it a score. Why does a sales of 1 get both a score of 1 and 2. Is my expression correct?
{<[Year-Month]=>}
{<[Current or Term*]= {'Current'}>}
Aggr(
if(aggr(count(sales),[Year-Month],[Employee ID]) <= fractile(TOTAL aggr(count(sales),[Year-Month],[Employee ID]), .2),5,
if(aggr(count(sales),[Year-Month],[Employee ID]) <= fractile(TOTAL aggr(count(sales),[Year-Month], [Employee ID]), .4),4,
if(aggr(count(sales),[Year-Month],[Employee ID]) <= fractile(TOTAL aggr(count(sales),[Year-Month], [Employee ID]), .6),3,
if(aggr(count(sales),[Year-Month],[Employee ID]) <= fractile(TOTAL aggr(count(sales),[Year-Month], [Employee ID]), .8),2,1)))),[Year-Month], [Employee ID])
IMO it's not a valid expression because you used a calculated dimension to compare a calculated dimension against an expression. The reason is that there is no outer aggregation around the aggr(). In some cases a NODISTINCT and/or a TOTAL statement may helpful but I suggest that you split at first the comparing parts in separate expressions to find the right ones before you tries to nest them within a cluster-logic.