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: 
narayanamsn
Creator
Creator

Aggregate functions - Fractile

Hi all,

I have an excel with 7 columns and used as source to Percentile QV.

Seller:

LOAD Seller,

    [Supply Time],

    [Part Name],

    Month,

    Delivery,

    Order,

    sub

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Now how to add RowNo() to this script.

Please find attached QV which is showing aggregated numbers even though month was selected as dimension.

Ex:


Filters : Delivery : Now and Tomorrow    Seller : D  Part Name : CPU


Jan'14 has 6440 which are less than 90th percentile of supply time (5).. But if you clear of selection on Month , it shows 6226 as the 90th percentile for overall data set is 3 and 6226 records have supply chain time less than 3.

How to get the 6440 against Jan even if I do not select month..

QVW is attached for the reference.

1 Solution

Accepted Solutions
sunny_talwar

This seems to work:

=Sum(Aggr(If([Supply Time] <= Fractile(TOTAL <Month> [Supply Time], 0.9), 1), Key, Month))


Capture.PNG

View solution in original post

4 Replies
sunny_talwar

My apologize, try this:

Script:

Seller:

LOAD RowNo() as Key,

    Seller,

    [Supply Time],

    [Part Name],

    Month,

    Delivery,

    Order,

    sub

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Expression:

=Count({<Delivery={'Tomorrow','Now'}, Key = {"=[Supply Time] <= Fractile(TOTAL <Month> [Supply Time], 0.9)"}>}[Supply Time])

sunny_talwar

This seems to work:

=Sum(Aggr(If([Supply Time] <= Fractile(TOTAL <Month> [Supply Time], 0.9), 1), Key, Month))


Capture.PNG

narayanamsn
Creator
Creator
Author

Great...

It is working fine... after many attempts

Kudos Sunny..

is there any difference " starting the expression with = and without = in variable declaration..

I know itz silly doubt..but I saw some changes when i deleted = from the script of Variable declaration

sunny_talwar

There is a difference, but to tell you the truth, I am still not 100% sure what the difference exactly is. I usually use a trial and error method to fix it

If you got what you wanted, I would ask you to close this thread by marking correct and helpful response so that other can benefit from this in the future.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny