Re: Basic help with fractile function - Page 2 - Qlik Community

Anonymous · ‎2017-08-08

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!

Anonymous · ‎2017-08-08

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?

sunny_talwar · ‎2017-08-08

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)

Anonymous · ‎2017-08-08

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?

sunny_talwar · ‎2017-08-08

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)

Anonymous · ‎2017-08-08

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.