Clustering / Aggregate a Count in a chart

[Riccardo]

Hello,

like the image, I have to do a count cluster:

# of Customer having a Sum(isVisit) = 1

# of Customer having a Sum(isVisit) = 2

# of Customer having a Sum(isVisit) >= 3

Do you know a best way to do it?

Thanks

[Riccardo]

Trying this, it works. But I'm not sure that is the best way:

DIM

=if(aggr(sum(isVisita),Customer) = 1,'1',
if(aggr(sum(isVisita),Customer) = 2,'2',
if(aggr(sum(isVisita),Customer) >=3,'3+')))

Measure: 

sum(isVisita)

[Kushal_Chawda · Accepted solution]

@Riccardo  try below

Dimension:

=valuelist('1 Visit','2 Visit','3+ Visit')

Expression:

=Pick(match(valuelist('1 Visit','2 Visit','3+ Visit'),'1 Visit','2 Visit','3+ Visit'),

Count({<CustomerID ={"=sum(isVisit)=1"}>} distinct CustomerID),

Count({<CustomerID ={"=sum(isVisit)=2"}>} distinct CustomerID),

Count({<CustomerID ={"=sum(isVisit)>=3"}>} distinct CustomerID))

[Kushal_Chawda]

@Riccardo  or below

Dimension:

=valuelist('1 Visit','2 Visit','3+ Visit')

Expression:

=Pick(match(valuelist('1 Visit','2 Visit','3+ Visit'),'1 Visit','2 Visit','3+ Visit'),

sum({<CustomerID ={"=sum(isVisit)=1"}>} isVisit),

sum({<CustomerID ={"=sum(isVisit)=2"}>} isVisit),

sum({<CustomerID ={"=sum(isVisit)>=3"}>} isVisit))

[MMS118]

Hi,

You can add a new dimension to your dashboard.

Perform the sum(isVisit) calculation on your load script as such.

TableName:

Load

AggregatingDimension,
IF(sum(isVisit)=1, 'isVisit=1', IF(sum(isVisit)=2, 'isVisit=2', 'isVisit=3')) as NewDimension

Resident OldTable
Group by AggregatingDimension;

Then you can simply have a bar chart with dimension = NewDimension and measure= count(Customer)

[Riccardo]

Thank you. I'm using this solution, and it works fine.

Just a question. If I click the bar chart of "2 Visit", I would like to select (filter) the Custoemrs of cluster "2 Visit". How can I do that?

Thanks