topic Re: Aggregate functions - Fractile in QlikView

Aggregate functions - Fractile

narayanamsn — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Aggregate functions - Fractile

sunny_talwar — Mon, 13 Jun 2016 20:32:58 GMT

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

Re: Aggregate functions - Fractile

sunny_talwar — Mon, 13 Jun 2016 20:56:15 GMT

This seems to work:

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

Re: Aggregate functions - Fractile

narayanamsn — Tue, 14 Jun 2016 15:27:23 GMT

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

Re: Aggregate functions - Fractile

sunny_talwar — Tue, 14 Jun 2016 15:36:25 GMT

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