0 Replies Latest reply: Dec 20, 2016 3:10 PM by Monica Chhabra RSS

    Quartile and use of fractile in the Qlik Sense

    Monica Chhabra

      Hi All,

       

      I have a table in data model with client ID & client name, Company ID & Company Name, Product Group, Product Sub-Category, Company Revenue, EE Segment.  A company ID has more than one Client ID and each client ID will have more than one Product Group and many Product Sub-Category (under each product group).  Each record has a Revenue by its company (called Company_Revenue) which is an aggregate of all client ID (under that company) all together for the product group and sub-category. For determining Revenue by each client ID i can use following expression: Sum(Aggr(Avg([Total_Company_Revenue__c]), [Client ID])) 

      Question 1) I want to calculate the Quartile for each client ID based on its revenue. I used the below expression in the measure of the table.  I am not sure if this is right way and it gives different quartiles for same client ID which is wrong.  Each client ID should have only one quartile based total revenue for all Client IDs.

      Syntax:

      'Q'&if(Sum(aggr(Avg([Company_Revenue]), [Client ID])) <= fractile(TOTAL aggr(Avg([Company_Revenue]), [Client ID]), 0.25), 1,

      if(Sum(aggr(Avg([Company_Revenue]), [Client ID])) <= fractile(TOTAL aggr(Avg([Company_Revenue]), [Client ID]), 0.50), 2,

      if(Sum(aggr(Avg([Company_Revenue]), [Client ID])) <= fractile(TOTAL aggr(Avg([Company_Revenue]), [Client ID]), 0.75), 3, 4)))

       

      Question 2) I want to achieve Quartile for each client ID based on Total count for Product Sub-Category sold under Client ID. I am not sure of it's syntax.

       

      I would really appreciate any help and leads on calculating quartile in this situation

       

      Thanks

      Monica

       

       

      Below is the snapshot of data from my table and an attachment of the table itself:

        

      Client NameClient IDCompany NameCompany IDProduct GroupProduct Sub-CategoryTenureTotal Company RenenueCompany CountClient CountClient QuartileClient Revenue
      Kreller Group Inc1139Kreller Group Inc.111000PayrollPayroll10+ Yrs$5k-$10k11Q35904.94
      Kreller Business Information Group Inc38952Kreller Group Inc.111000BenefitsACA10+ Yrs$5k-$10k11Q40
      Kreller Business Information Group Inc38952Kreller Group Inc.111000HRACA Year End10+ Yrs$5k-$10k11Q40