Counting null and specific values together

[jamesmf1982]

Hi,

I am putting together an expression at the moment which the number of visits in a table where a field (in this case VisitVariationReason) is set to either null or not 'Entry Error' between a date range.

I have the following:

count( {<VisitStartDate={">=$(=$(v_date_min))<=$(=date($(v_date_min)+28))"}> } VisitCounter)

Which counts all the visits fine (including null and non null VisitVariationReason entries)

NB: VisitStartDate is a date field and v_date_min is the selection from calendar.

However when I add the following

count( {<VisitStartDate={">=$(=$(v_date_min))<=$(=date($(v_date_min)+28))"}, VisitVariationReason -= {'Entry Error'}> } VisitCounter)

It returns any reason with a variation reason that doesn't equal Entry Error but doesn't return any null entries.

How would I put something together to return both? If it's easier to do it as an if statement I am happy to do that.

Thanks

James

[sunny_talwar · Accepted solution]

May be create a flag in the script

If(VisitVariationReason <> 'Entry Error', 1, 0) as Flag

and then use this

Count( {<VisitStartDate={">=$(=$(v_date_min))<=$(=date($(v_date_min)+28))"}, Flag = {'1'}>} VisitCounter)

[jamesmf1982]

Hi Sunny,

Thanks will try this. When adding it in do I need to use a LOAD function before the if statement?

[sunny_talwar]

Whereever you have the field VisitVariationReason, just add this to the same load

TableName:

LOAD VisitVariationReason,

     If(VisitVariationReason <> 'Entry Error', 1, 0) as Flag,

     OtherFields

FROM ...;

[jamesmf1982]

Many thanks Sunny!

[sunny_talwar]

No problem at all