topic Re: Distinct count by month without double counting in QlikView

Distinct count by month without double counting

devan9876 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Distinct count by month without double counting

ziadm — Fri, 26 May 2017 15:27:12 GMT

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

Re: Distinct count by month without double counting

ziadm — Fri, 26 May 2017 15:29:12 GMT

Chart --Straight or Pivot Table

Dimension -- Month Name

Expression -- Count(Distinct SN)

Re: Distinct count by month without double counting

devan9876 — Fri, 26 May 2017 15:46:45 GMT

In a chart.

Re: Distinct count by month without double counting

Gysbert_Wassenaar — Fri, 26 May 2017 15:47:28 GMT

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)

Re: Distinct count by month without double counting

ziadm — Fri, 26 May 2017 15:52:33 GMT

Dimension MonthName

Sorry Expression will be

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

Re: Distinct count by month without double counting

alis2063 — Fri, 26 May 2017 17:52:08 GMT

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

Re: Distinct count by month without double counting

alis2063 — Fri, 26 May 2017 18:17:05 GMT

find output

Re: Distinct count by month without double counting

Sergey_Shuklin — Sat, 27 May 2017 13:08:57 GMT

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! !