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: 
devan9876
Creator
Creator

Distinct count by month without double counting

Hi,

I want a to calculate unique count of Serial Numbers tested in each month. The relevant fields being used are "SN" and "MonthYear". I tried to use Count( Distinct(SN) but the problem is some serial numbers were tested across more than one month and I don't want them to be double counted.

For example...

Jan-16

SN1

SN2

SN3

Feb-16

SN3

SN4

SN5

In this scenario Count( Distinct(SN) would return a count of 3 for both months, but what I want is for 3 to be returned for Jan-16 and 2 to be returned for Feb-16.

Any suggestions?

1 Solution

Accepted Solutions
ziadm
Specialist
Specialist

Dimension MonthName

Sorry Expression will be

Count(aggr(Count ( {1<MonthName = >} DISTINCT  SN) , SN))

View solution in original post

8 Replies
ziadm
Specialist
Specialist

are you trying to do this in the front (Tables Charts) or in the Data Model

ziadm
Specialist
Specialist

Chart --Straight or Pivot Table

Dimension -- Month Name

Expression -- Count(Distinct SN)

devan9876
Creator
Creator
Author

In a chart.

Gysbert_Wassenaar

Add an extra field in the script that keeps track of when a SN first appeared:

LOAD

     Month,

     SN,

     If(Not Exists(SN),1,0) as IsNewSN

FROM

     ....

Then use that new field in a set analysis expression: count({<IsNewSN={1}>} distinct SN)


talk is cheap, supply exceeds demand
ziadm
Specialist
Specialist

Dimension MonthName

Sorry Expression will be

Count(aggr(Count ( {1<MonthName = >} DISTINCT  SN) , SN))

alis2063
Creator III
Creator III

You will have to write the script as below to fulfill your requirement,

[Jan]:

load * inline [ID,Month

SN1,Jan

SN2,Jan

SN3,Jan

];

Feb:

load * where not exists(ID);

load * inline [ID,Month

SN3,Feb

SN4,Feb

SN5,Feb

];

output

alis2063
Creator III
Creator III

find outputoutput.png

Sergey_Shuklin
Specialist
Specialist

Hello, Devan!

If we talk about fixed values of field like months and you don't want to change your script, you can use this non-trivial method ↓

(The disadvantage of it's for each month you'll have to create a separate expression)

For 'Jan' it will be: Count(DISTINCT {<mnt={'Jan'}>}sn)

For 'Feb': Count(DISTINCT {<mnt={'Feb'},sn=e({<mnt={'Jan'}>}sn)>}sn)

For 'Mar': Count(DISTINCT {<mnt={'Mar'},sn=e({<mnt={'Jan','Feb'}>}sn)>}sn)

and so on.

Yeah, looks ugly, but it works 😃

May be you'll find it usefull! !