5 Replies Latest reply: Jan 4, 2017 5:11 PM by Deborah Biardo

# Get count distinct if max value in Pivot Table

Hi,

Please, I need help with this problem.

I have a dataset where each row is a customer per month. The fields are: name (of  customer-Empresa), month/year (MESAÑO) and Status(IdEstatus - containing four values: 1 -active, 2 - at risk, 3 - loss, 4 inactive ).

I need to show on a pivot table the quatity of distinct customers with status=2 (1,2,3,4) by month and quarter. For each row of the pivot table (month or quarter) I need the count of different customers if the max(Status)=2 (in that period)

The pivot table should look something like this (all numbers are drawn from the dataset)

I have tried:

IF(ISNULL(aggr (MAX((IdEstatus)),[MESAÑO])), aggr (MAX((IdEstatus)),Tri),aggr (MAX((IdEstatus)),[MESAÑO])).

but when I try to count this it does not work.

Other approach was:

COUNT(DISTINCT IF(aggr(NODISTINCT max(IdEstatus),EMPRESA)=2,EMPRESA)) but gives me all zeros

 Quarter Month/Year Count of Customers with Status=2 2 0 06/2016 0 3 11 07/2016 2 08/2016 7 09/2016 9 4 12 10/2016 8 11/2016 8

This?

• ###### Re: Get count distinct if max value in Pivot Table

with your excel data set and this expression

count(DISTINCT if(aggr(max(Status), Quarter, Month, Name)=2,  Name))

I get this

• ###### Re: Get count distinct if max value in Pivot Table

I have tried it but I have this problem when I test it at customer level. (e.g. Company number2)

In this case, in the fourth quarter, it should have shown zero because the maximum was 3 in Q4.

• ###### Re: Get count distinct if max value in Pivot Table

si in 4 quarter you want

0 for quarter

1 for october

0 for november

?

• ###### Re: Get count distinct if max value in Pivot Table

maxgro

Yes, I want that!!

Because, the max Status for Company 2 in Q4 is not 2.

I take your suggestion and make this:

if(dimensionality()=1, count(distinct If(aggr ( nodistinct max(IdEstatus),Tri,EMPRESA)=2, EMPRESA)),

count(distinct If(aggr ( nodistinct max(IdEstatus),[MESAÑO],EMPRESA)=2, EMPRESA)))

But, this is not the best solution becasue when users change the dimensions on the pivot it does not work.

Can you give me a different solution for this problem,please?

Tnks Deborah