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

[aurly123]

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)

[Frank_Hartmann]

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)

[aurly123]

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

[aurly123]

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.

[kaushiknsolanki]

HI,

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

[aurly123]

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

[rubenmarin · Accepted solution]

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

[aurly123]

Thanks Ruben, will try and update