Distinct count by month without double counting

[devan9876]

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?

[ziadm]

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

[ziadm]

Chart --Straight or Pivot Table

Dimension -- Month Name

Expression -- Count(Distinct SN)

[devan9876]

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)

[ziadm · Accepted solution]

Dimension MonthName

Sorry Expression will be

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

[alis2063]

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]

find output

[Sergey_Shuklin]

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