Get count distinct if max value in Pivot Table

[#1]

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

Thanks for your help !!!

[sunny_talwar]

This?

[maxgro]

with your excel data set and this expression

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

I get this

[#4]

Thanks both for the answers.

maxgro‌.

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.

[maxgro]

si in 4 quarter you want

0 for quarter

     1 for october

     0 for november

?

[#6]

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