topic Re: Set expressions to exclude nulls and count distinct elements not working. in App Development

Set expressions to exclude nulls and count distinct elements not working.

rmadursk — Fri, 19 May 2023 19:51:05 GMT

I have a set of data that looks like this:

Attested_Date	Service
3/31/2023	satellite01
3/31/2023	wbscvecc11
3/31/2023	wbscvecc11
12/31/2023	rtvspx01
12/31/2023	rtvspx01
6/30/2024	triprh701
6/30/2025	wbscvecm04
6/30/2025	wbscvecm04
12/31/2025	spcsgridn02
12/31/2025	spcsgridn02
12/31/2025	spcsgsub01
12/31/2025	spcsmtier01
12/31/2025	spcsmdata01
-	wbscvemp21
-	wbscvmdm01
-	wbscvmdm01
-	wbscvmgr01
-	wbscvmgr90

I need to know how many Distinct Service names have an Attested_Date.

I've tried the following based on other Community posts:

//Count({$<[Attested_Date] = {"=not isnull([ATTESTED_DATE] )"}>} DISTINCT([Service]))
//Count({$<[Attested_Date] -= "=Null()">} DISTINCT([Service]))
//Count({$<[Attested_Date] = {'*'}>} DISTINCT([Service]))
//Count({$<[Attested_Date] = {"=Len(Trim([Attested_Date])) > 0"}>} DISTINCT([Service]))
//Count({$<[Attested_Date] = {'*'}-{''}>} DISTINCT([Service]))

These all just report back the count of distinct services (14) when it should be 9.

This expression appeared to be working a couple of days ago but stopped for some reason. I don't think it is the app since I created a new app with just this table and I get the same results. The Null fields appear to be actual Null fields because they show up in Data Manager Preview Pane as Nulls.

Re: Set expressions to exclude nulls and count distinct elements not working.

Lisa_P — Fri, 19 May 2023 21:01:29 GMT

Try this:

Count({<Attested_Date={"=len(Attested_Date)>1"}>}Distinct Service)

Re: Set expressions to exclude nulls and count distinct elements not working.

rmadursk — Fri, 19 May 2023 21:06:45 GMT

Nope, same thing. I'm really starting to wonder if there isn't something funny in the data that I can't see.

Thanks,

Re: Set expressions to exclude nulls and count distinct elements not working.

Vegar — Fri, 19 May 2023 21:43:06 GMT

I think you where almost there in one of your attempts. Try this:

Count({$<[Attested_Date] = {"*"}>} DISTINCT([Service]))

Re: Set expressions to exclude nulls and count distinct elements not working.

BrunPierre — Fri, 19 May 2023 22:23:26 GMT

Count({$<[Attested_Date] = {"=Len(Trim([Attested_Date])) > 1"}>} DISTINCT([Service]))

Re: Set expressions to exclude nulls and count distinct elements not working.

marcus_sommer — Mon, 22 May 2023 11:11:14 GMT

The simplest way to handle such scenarios is to replace the NULL with a real value within the data-model, for example with:

coalesce(Attested_Date, '<NULL>') as Attested_Date

if(len(trim(Attested_Date)), Attested_Date, '<NULL>') as Attested_Date

and then you could directly select <NULL> as value or using it as condition within a set analysis.

Re: Set expressions to exclude nulls and count distinct elements not working.

rmadursk — Mon, 22 May 2023 16:32:27 GMT

I considered this as an option but hadn't tried it yet since, at least in my mind, the option of just ignoring the NULLs seemed appropriate.

The coalesce function is unique. I am going to need to play around with that one.

Thanks,

Ron

Re: Set expressions to exclude nulls and count distinct elements not working.

rmadursk — Mon, 22 May 2023 16:36:31 GMT

This one works.

I'm not going back to check all the ones that I tried previously but my original formula also works again. There must have been an anomaly somewhere that I was unaware of and couldn't find.

Thanks for the help.

Ron

Re: Set expressions to exclude nulls and count distinct elements not working.

rmadursk — Mon, 22 May 2023 16:37:16 GMT

This one works also. It is very close to my original formula.

I'm not going back to check all the ones that I tried previously but my original formula also works again. There must have been an anomaly somewhere that I was unaware of and couldn't find.

Thanks for the help.

Ron

Re: Set expressions to exclude nulls and count distinct elements not working.

barnabyd — Mon, 22 May 2023 21:37:29 GMT

G'day @rmadursk, I would take this one step further. What I think you are doing is counting how many distinct services there are that have been attested. I take it that being attested is an important business concept. Therefore, I would create a flag in the load script first:

if( isnull( Attested_Date ), 0, 1 ) as isAttested

Then the set analysis becomes trivial:

Count( {$<isAttested={1}>} DISTINCT([Service]) )

I hope this is a useful comment for someone.

Cheers, Barnaby.

Re: Set expressions to exclude nulls and count distinct elements not working.

rmadursk — Tue, 23 May 2023 12:46:04 GMT

I really like this idea. There are several fields/expressions that I think I can use this tactic to make my sheets less complicated. Just have to remember to do them all before I split and unpivot the troublesome column.