Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Any way to make this formula work?

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))

24 Replies
Anonymous
Not applicable
Author

upload sampel qvw

Anonymous
Not applicable
Author

HEre, thanks for helping!

Anonymous
Not applicable
Author

Here you go, thanks for helping!

Anonymous
Not applicable
Author

Doesn't work

sunny_talwar

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

Anonymous
Not applicable
Author

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

sunny_talwar

That is definitely true, things in script are not very dynamic. Let explore it in the front end itself then

Best,

Sunny

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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%

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author