Any way to make this formula work?

[Anonymous]

I cant get this formula to work in a chart expression, I've tried with aggr and putting the expression in a variable, not sure if possible or what am I doing wrong

=count(if(FCT<Fractile(FCT,0.9),PO))

[Anonymous]

Is not the same case he wanted to apply fractile to an aggregation, I just want to be able to compare 1 field vs the result of fractile, thanks

[Anonymous]

Ok this is the best I have:

count(if(FCT<MAX(TOTAL Aggr(Fractile(FCT,0.9),Wk_Num)),FCT)))

This gives me the number I'm looking for, but only for weeks 49 & 50 because I'm taking the max (see gray line)

If I change for min, it also gives me the right numbers but only for weeks 46 to 48 (again see gray line)

I tried with Only instead of MIN/MAX but it doesn't work.....

Any ideas?? Thanks in advance

[Anonymous · Accepted solution]

This works:

count(if(FCT < aggr(NODISTINCT Fractile(total<Wk_Num> FCT, 0.9),Wk_Num), PO))

See attached.  You'll probably need to replace the Wk_Num with the current field of the cycle group.

Edit: In fact, it works without total as well:

count(if(FCT < aggr(NODISTINCT Fractile(FCT,0.9),Wk_Num), PO))

The secret word is still NODISTINCT

[Anonymous]

Yay! you're my hero, thanks a lot Mike!!

[Anonymous]