topic Re: Independent Summary count for each customer based on percentage from straight table in QlikView

Independent Summary count for each customer based on percentage from straight table

aurly123 — Sat, 16 Nov 2024 00:51:52 GMT

Hi,

I want to get the count of each customer based on the utilization percentage and group the count under each percentage range (ie, <50% , 50-70%, 70-90% and >90%). Attached the input data and straight table.. Please share your inputs on how to get this designed. Thanks!

Attached the screenshot (count may not be accurate in the screenshot)

Re: Independent Summary count for each customer based on percentage from straight table

Frank_Hartmann — Fri, 10 Apr 2020 07:39:08 GMT

Script:

Load *, if(Utilisation%<0.5,'<50%', if(Utilisation%>=0.5 and Utilisation%<=0.7 ,'50 to 70%', if(Utilisation%>=0.7 and Utilisation%<=0.9 ,'70 to 90%','>90%'))) as Interval; LOAD * INLINE [ Customer, Identifier, Utilisation% BANK OF CHINA, A1, 0.5 BANK OF CHINA, A2, 0.69 BANK OF CHINA, A3, 0.89 BANK OF CHINA, A4, 0.99 STATE BANK, F1, 0.11 STATE BANK, F2, 0.56 STATE BANK, F3, 0.77 STATE BANK, F4, 0.9 WHO, G1, 0.25 WHO, G2, 0.55 WHO, G3, 0.75 WHO, G4, 0.92 BOA, N1, 0.33 BOA, N2, 0.63 BOA, N3, 0.78 BOA, N4, 0.96 ];

Pivottable:

Dim1 = Interval

Dim2 = Customer

Expression: count(Utilisation%)

(Drag the Customer dimension to the upper right untill the blue horizontal line appears)

Re: Independent Summary count for each customer based on percentage from straight table

aurly123 — Fri, 10 Apr 2020 07:40:16 GMT

I created a pivot chart and retrieved the count for <50 % (ie <0.5) and >90% using set analysis. I am unable to get the count for in between ranges using set analysis.

expression used for <50% and >90%

=count({<[Utilisation %]={"<=0.5"}>}Customer)

=count({<[Utilisation %]={">=0.9"}>}Customer)

what expression can be used to get the in between ranges. (ie, between 50% to 70% and 70% to 80%)?

Attached the PIVOT Chart table for reference

Re: Independent Summary count for each customer based on percentage from straight table

aurly123 — Fri, 10 Apr 2020 07:48:49 GMT

Hi Frank,

Thanks for the reply. We cannot bring the data inside INLINE table since the data would growing in the input excel. Actually Customer and Identifier is the only dimension available from the input sources and Utilization field is actually a calculated dimension. I just tried to create a pivot table and derived the count using set analysis however i could not retrieve the count between 50 to 70 and 70 to 90. Is that possible?

Attached the screenshot.

Re: Independent Summary count for each customer based on percentage from straight table

kaushiknsolanki — Fri, 10 Apr 2020 07:56:36 GMT

HI,

Can you share the sample data in excel along with the calculation for Utilization.

Re: Independent Summary count for each customer based on percentage from straight table

aurly123 — Fri, 10 Apr 2020 08:08:14 GMT

Hi Kaushik,

I have attached the input data. Utilisation % is calculated using dimensions from various other tables so i have included this manually in the excel sheet. Please take this a look. Thanks

Re: Independent Summary count for each customer based on percentage from straight table

rubenmarin — Wed, 15 Apr 2020 10:39:13 GMT

Hi, can be the attached sample a solution for this?

Re: Independent Summary count for each customer based on percentage from straight table

aurly123 — Thu, 16 Apr 2020 10:06:31 GMT

Thanks Ruben, will try and update