Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Quartile and use of fractile in the Qlik Sense

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
0 Replies