Count, IF and Distinct Query Syntax

[#1]

Need scripting help with an Expression.  I need to count DISTINCT based on two fields, but now I need to add an IF as well.

So, I need to count the number of DISTINCT events (defined by combination of IDKEY and ServiceDate) IF the POTCode is 23.

So far, I have tried numerous combinations.  The following are a couple examples, but none of them work.  Can someone please help me with the syntax?

IF(POTCode = '23', Count(DISTINCT IDKey& ServiceDate))  --- I get no results with this one.

Count({$<POTCode={"23"}>}DISTINCT IDKey & ServiceDate) --The totals are not correct as it is still pulling other POT codes and repeat ServiceDates.

Please help.

[MK_QSL · Accepted solution]

Count(Distinct IF(POTCode = '23', IDKey& ServiceDate))

[nigel987]

Hi,

maybe there is an easier way, but you could create a new field out of IDKEY and ServiceDate and then you could count the distinct values of this field.

KR

[MK_QSL]

Better way is to create a key from combination of IDKey & ServiceDate..

AutoNumber(IDKey& ServiceDate) as Key

Now you can use

Count(Distinct If(POTCode = '23', Key))