How to Find upper quartile

[GeorgeEard]

Hi,

How would I be able to find the upper quartile of this measure that is already finding the average?

(((count (distinct {<[Initial Decision]={'Accept','Conditional Accept','Fraud Refer'}>} [PCCA ID])))+

(count (distinct {<[Initial Decision]={'Credit Refer'},[Final Decision]={'Accept','Conditional Accept'}>} [PCCA ID])))

/(Count(distinct [PCCA ID]))

Any help would be greatly appreciated.

[BPiotrowski]

Hi, @GeorgeEard 

So the solution would be variable in load script:

let nth = Peek('value',Ceil(NoOfRows('table')*3/4)-1,'table');

where value is column name and table is table name.

[GeorgeEard]

Hi, I just tried this and it's not quite working. Do you have any other ideas?

[BPiotrowski]

How do you use this upper quartile as KPI or in measure show me how you used it ?

[GeorgeEard]

Unsure if I did it correctly

[BPiotrowski]

U just need:

or =$(nth)

[GeorgeEard]

Hi I'm still getting no luck, have also set the expression to = $(nth)

[marcus_sommer]

I assume your solution may go rather in this direction:

fractile(aggr(YourExpression, Dim1, Dim2), 0.75)

which is a nested aggregation against the needed dimensionality - Dim1 and Dim2 are just place-holder for the real ones.

[GeorgeEard]

Hi I've just tried:

fractile(aggr((((count (distinct {<[Initial Decision]={'Accept','Conditional Accept','Fraud Refer'}>} [PCCA ID])))+

(count (distinct {<[Initial Decision]={'Credit Refer'},[Final Decision]={'Accept','Conditional Accept'}>} [PCCA ID])))

/(Count(distinct [PCCA ID])), dim1, dim2), 0.75) 

And again no luck, any other suggestions would be great but if not no worries.

[marcus_sommer]

You need to adjust the dimensional context - because like hinted the Dim1 and Dim2 are only place-holder for your existing ones.

I suggest you play at first a bit with the logic in another table and something like:

fractile(aggr(count(myField), myCategory), 0.75)

and then replacing the dimension and/or adding a second/third one to see what happens. If this is clear you may add step by step more complexity with conditions or rate-calculations or ...