Create new category dimension based on field values

[GeromA]

Hi all,

I'd like to create a dimension in QS to help me categorizing my customers into clusters. Unfortunately, I keep getting the 'invalid dimension' error.

I have a table like this:

Customer	Consumption
Customer A	10000
Customer B	420000
Customer C	900000
Customer D	65000
Customer E	135000
Customer F	152000
Customer G	7000
Customer H	3530

The clustering formula should do the following:

=if([Consumption]<12001,'0 - 12.000',

if([Consumption]<75001,'12.001 - 75.000',

if([Consumption]<180001,'75.001 - 180.000','180.001 - 1.500.000')))

In addition, there are two limiting criteria that are needed to identify whether the consumption is to be counted.

The final result should look like this:

Cluster	Customer	Consumption
0 - 12.000	Customer A	10000
180.001 - 1.500.000	Customer B	420000
180.001 - 1.500.000	Customer C	900000
12.001 - 75.000	Customer D	65000
75.001 - 180.000	Customer E	135000
75.001 - 180.000	Customer F	152000
0 - 12.000	Customer G	7000
0 - 12.000	Customer H	3530

Many thanks for your solutions.

Best,

GeromA

[agigliotti · Accepted solution]

Hi @GeromA ,

You should add a calculated dimension as below:

aggr( if( sum(Consumption)<12001, '0 - 12.000',
if( sum(Consumption)<75001, '12.001 - 75.000',
if( sum(Consumption)<180001, '75.001 - 180.000', '180.001 - 1.500.000' ))), Customer )

I hope it can helps.
Best Regards

[GeromA]

Hi @agigliotti ,

thanks, this finally did the trick.

Best,

GeromA