16 Replies Latest reply: Aug 9, 2017 12:31 PM by loganayaki ramachandran

# Basic help with fractile function

Hi Friends,

Need some help with the fractile function in Qlik sense. I am trying to explore this function to implement the decile functionality that I am looking for. I have customer with Product Quantity. I want to create 10 equal distributions based on this Product quantity.

I say "Fractile(TOTAL Quantity, 0.10)". It gives me back 0.

Any help would be appreciated?

Thanks!

• ###### Re: Basic help with fractile function

Where exactly are you using the expression?

And how do your data look like? Could you post some sample records?

• ###### Re: Basic help with fractile function

Guessing a bit as to your precise needs, but maybe the Class() function could be more useful, here is the Help url for it.

https://help.qlik.com/en-US/sense/June2017/Subsystems/Hub/Content/Scripting/ConditionalFunctions/class.htm

• ###### Re: Basic help with fractile function

I need something like - the list of customer names falling under 90 - 100 % decile, 80 - 90 % etc.

Cust A - 90 - 100% or  1st decile

Cust B - 90 - 100% or 1st decile

Cust C - 80 - 90% or 2nd decile

• ###### Re: Basic help with fractile function

Your Quantity column is an expression like Sum(FIELD), right?

• ###### Re: Basic help with fractile function

That is correct

• ###### Re: Basic help with fractile function

You would need to create a synthetic table with dimension Customer and your Quantity expression and then apply the fractile function on top of it, assuming Sum(FIELD) as your Quantity expression and Customer field as your grouping entity:

=Fractile( Aggr( Sum(Quantity), Customer), 0.1)

• ###### Re: Basic help with fractile function

Not sure if I did the calculation right, but this is my output.. Not really working the way I wanted

• ###### Re: Basic help with fractile function

In the chart, you might need to use TOTAL Qualifier

Fractile(TOTAL Aggr(Sum(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.1)

• ###### Re: Basic help with fractile function

When I did this Fractile(TOTAL Aggr(Sum(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.1) it gave me just 0.00 for all customers. Not sure why.

I tried doing the below, I am getting some non-zero output now,

=if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.1), 10,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.2), 9,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.3), 8,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.4), 7,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.5), 6,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.6), 5,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.7), 4,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.8), 3,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.9), 2, 1)))))))))

trying to validate the same.

Is there a way I can filter to show only the customers that fall in Category '1' in the above logic, not show the customers in other categories in my visualization?

• ###### Re: Basic help with fractile function

Can you try to check if there are a lot of customers with 0 value causing the bottom 10 percent value to be 0? Check what do you get when you use this

Fractile(TOTAL Aggr(Sum(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.9)

Also, may be you want to exclude Customers with 0 value to calculate your Fractile.... for that, may be try this

Fractile({<SHIPTO_CUSTOMER_GROUP_NAME -= {0}>}TOTAL Aggr(Sum({<SHIPTO_CUSTOMER_GROUP_NAME -= {0}>} Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.1)

• ###### Re: Basic help with fractile function

That is absolutely right! Looks like I had a lot of customer entries with 0 quantity which was skewing my results a lot.

I tried eliminating zeros as suggested and results look better. I had to make one change to the formula, replaced 'SHIPTO_CUSTOMER_GROUP_NAME' in RED with the 'Count'

Hope that is right

Fractile({<Count -= {0}>}TOTAL Aggr(Sum({<Count -= {0}>} Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.1)

Can you help with this filter? Is there a way I can filter to show only the customers that fall in Category '1' in the above logic, not show the customers in other categories (2 to 9) in my visualization?

• ###### Re: Basic help with fractile function

I wanted to use this:

Fractile({<SHIPTO_CUSTOMER_GROUP_NAME = {"=Sum(Count) <> 0"}>}TOTAL Aggr(Sum({<SHIPTO_CUSTOMER_GROUP_NAME = {"=Sum(Count) <> 0"}>} Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.1)

Not sure if it gives the same output or not, but I think this makes more sense.

Coming to you other question, may be you need this?

Fractile({<SHIPTO_CUSTOMER_GROUP_NAME = {"=Sum({<Category = {1}>}Count) <> 0"}, Category = {1}>}TOTAL Aggr(Sum({<SHIPTO_CUSTOMER_GROUP_NAME = {"=Sum({<Category = {1}>}Count) <> 0"}, Category = {1}>} Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.1)

• ###### Re: Basic help with fractile function

I will check the other formula and let you know if it works the same way.

Regarding the filter there is no field called Category. In the below logic we assign the numbers 1 to 10. I want to display just the customers assigned the number 1  in the visualization.

=if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.1), 10,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.2), 9,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.3), 8,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.4), 7,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.5), 6,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.6), 5,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.7), 4,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.8), 3,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.9), 2, 1)))))))))

• ###### Re: Basic help with fractile function

Maybe you can achieve what you want a little easier.

Try as expression in your chart:

= Ceil( (Rank(Sum(Count)) / Count(DISTINCT TOTAL SHIPTO_CUSTOMER_GROUP_NAME)),0.1)*10

Or create a filter pane with an expression like

=Aggr(

Only({1} Ceil( (Rank(Sum(Count)) / Count(DISTINCT TOTAL SHIPTO_CUSTOMER_GROUP_NAME)),0.1)*10),

SHIPTO_CUSTOMER_GROUP_NAME

)

to be able to filter your new category values.

If your Sum(Count) values per dimension could be ties, you can use the additional parameter to the Rank() function to break the ties.

If you need to filter e.g. Count values different from zero, add set analysis as proposed above.

Hope this helps

Stefan

• ###### Re: Basic help with fractile function

Hi Stefan,

I am not sure I quite follow your formula here as to how it works,

= Ceil( (Rank(Sum(Count)) / Count(DISTINCT TOTAL SHIPTO_CUSTOMER_GROUP_NAME)),0.1)*10

I tried putting this expression as a field and compared it against another field using the fractile formula below,

=if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.1), 10,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.2), 9,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.3), 8,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.4), 7,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.5), 6,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.6), 5,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.7), 4,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.8), 3,

if(SUM(Count) <= Fractile( TOTAL Aggr( SUM(Count), SHIPTO_CUSTOMER_GROUP_NAME), 0.9), 2, 1)))))))))

The results dont seem to match.