Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

Thanks for your help !!!

5 Replies
sunny_talwar

This?

Capture.PNG

maxgro
MVP
MVP

with your excel data set and this expression

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

I get this

1.png

Not applicable
Author

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.

Empresa2.png

maxgro
MVP
MVP

si in 4 quarter you want

0 for quarter

     1 for october

     0 for november

?

Not applicable
Author

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