How to get a count of the number of Customers greater than a factile?

[#1]

Hi there,

I am trying to get dynamic counts of the number of customers above a fractile

Here is what I have so far, which results in nothing

<h4>Count(DISTINCT if((aggr(SUM(Renewed),[ Account Name])/aggr(SUM([Quantity]),[ Account Name]))>=fractile(AGGR(Sum(Renewed),[Account Name])/AGGR(sum([ Quantity]), [ Account Name]),0.75),[ Account Name]))

If I remove the fractile calculation (after >=), and replace it with, for example 0.9 then the code evaluates correctly.

Any insight appreciated.

Thanks, James.

[pover]

Try testing the aggr function in a calculate dimension to see what values it returns. You might want to try changing the aggr function to something like this:

AGGR(Sum(Renewed)/sum([Quantity]), [ Account Name])

or

AGGR(Sum(Renewed/[Quantity]), [ Account Name])

Regards.

[#3]

Thanks Karl.

I can only get it to work when I use the aggr function on both the numerator and denominator.

Additionally, if I take the factile part of the calculation and use it on its own it works.

[#4]

Hopefully adding some clarity:

This returns the correct count

<h4>Count(DISTINCT if(aggr(sum(Renewed),[Supporting Reseller Account Name])/aggr(sum([Renewal Quantity]),[Supporting Reseller Account Name])<0.9,[Supporting Reseller Account Name])) </h4>

If I then replace the '0.9' with another line of code which calculates the upper quartile and works on its own, then it breaks- any ideas?

<h4>fractile(AGGR(Sum(Renewed),[Supporting Reseller Account Name])/AGGR(sum([Renewal Quantity]), [Supporting Reseller Account Name]),0.75) </h4>

[pover · Accepted solution]

When you put the fractile expression within the other expression, put the TOTAL keyword.

fractile(TOTAL AGGR(Sum(Renewed),[Supporting Reseller Account Name])/AGGR(sum([Renewal Quantity]), [Supporting Reseller Account Name]),0.75)

This seemed to work for me in the example I was playing with. I've never played with the fractile expression much, but now that I've seen what it does, I'm going to start using it.

Regards.

[#6]

Thank you Karl. Works perfectly