Count formula consider blank field in count

Please check below snap facing issue while using count function.

L1M_Trans does not contain any value still count result in 1.

please suggest the correct way to do this.

Formula :- IF DateTimeFormat([Trans_Date],"%Y-%m-%d") >= "2015-12-01" && DateTimeFormat([Trans_Date],"%Y-%m-%d") <= "2015-12-31" THEN [Trans_Id] ELSE '' ENDIF

in summarize function just taking the distinct not null count

same issue with L4M_Trans field

Transformation

Preparation

Expression

Accepted answers

jdunkerley79

Adjust the formula so that it is:

IF DateTimeFormat([Trans_Date],"%Y-%m-%d") >= "2015-12-01" && DateTimeFormat([Trans_Date],"%Y-%m-%d") <= "2015-12-31" THEN [Trans_Id] ELSE Null() ENDIF

Then you should be able to use the Count Distinct Not Null in the summarize tool

All comments

jdunkerley79

Adjust the formula so that it is:

IF DateTimeFormat([Trans_Date],"%Y-%m-%d") >= "2015-12-01" && DateTimeFormat([Trans_Date],"%Y-%m-%d") <= "2015-12-31" THEN [Trans_Id] ELSE Null() ENDIF

Then you should be able to use the Count Distinct Not Null in the summarize tool

AndrewW

It looks like your field L1M_Trans does contain a value, probably a zero length string. You're trying to count non-null values, which is what it's doing as a NULL and blank (zero length string) are different. To test this try counting the non blanks (under the string area of the Summarise tool) and see what you come out with.

vizay_jadhav

Thanks a lot for Quick help jdunkerley79 Smiley Happy

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335