Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
upload sampel qvw
HEre, thanks for helping!
Here you go, thanks for helping!
Doesn't work
One way to do is to do the Fractile calculation in the script like this:
Table:
LOAD * INLINE [
DIM, FCT, PO
A, 10, 1
A, 21, 2
A, 63, 1
A, 12, 2
A, 21, 2
A, 25, 1
B, 65, 2
B, 89, 2
B, 84, 3
B, 20, 1
B, 10, 23
B, 11, 1
C, 80, 2
C, 20, 31
C, 30, 3
C, 3, 1
C, 1, 12
];
Join(Table)
LOAD DIM,
Fractile(FCT, 0.90) as [90Percentile]
Resident Table
Group By DIM;
and then your expression could look like this:
=Count(If(FCT < [90Percentile], PO))
HTH
Best,
Sunny
Hi Sunny, the problem with that is that I'd make it work for just one dimension and lose the dynamic capability, as the user will be able to combine up to 9 different dims
That is definitely true, things in script are not very dynamic. Let explore it in the front end itself then
Best,
Sunny
I don't really get it.
Why not
=count( PO ) * 0.9 // Counting the # of PO's with an FCT value below fractile which is ... 90%
Hi Peter
=count(PO) * 0.9 would give me how many POs represent 90%
Fractile returns the 90th PO value when they are sorted ascending
Quick example:
1
1
1
2
2
3
count * 0.9 = 5.4
Fractile = 2.5