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

    Basic help with fractile function

    loganayaki ramachandran

      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
          Stefan Wühl

          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
              Bill Markham

              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
                  loganayaki ramachandran

                  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
                    Stefan Wühl

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

                        • Re: Basic help with fractile function
                          Stefan Wühl

                          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
                              loganayaki ramachandran

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

                               

                              8-8-2017 2-10-41 PM.jpg

                                • Re: Basic help with fractile function
                                  Sunny Talwar

                                  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
                                      loganayaki ramachandran

                                      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
                                          Sunny Talwar

                                          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
                                              loganayaki ramachandran

                                              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
                                                  Sunny Talwar

                                                  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
                                                      loganayaki ramachandran

                                                      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
                                                          Stefan Wühl

                                                          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
                                                              loganayaki ramachandran

                                                              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.