Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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

Re: Get count distinct if max value in Pivot Table

This?

Capture.PNG

MVP
MVP

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

1.png

Not applicable

Re: Get count distinct if max value in Pivot Table

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

MVP
MVP

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

?

Not applicable

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