Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
rmadursk
Contributor III
Contributor III

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

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.

Labels (1)
2 Solutions

Accepted Solutions
Vegar
MVP
MVP

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

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

 

View solution in original post

barnabyd
Partner - Creator III
Partner - Creator III

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.

Barnaby Dunn
BI Consultant

View solution in original post

10 Replies
Lisa_P
Employee
Employee

Try this:

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

rmadursk
Contributor III
Contributor III
Author

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

Thanks,

 

Vegar
MVP
MVP

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

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

 

BrunPierre
Partner - Master
Partner - Master

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

BrunPierre_0-1684534961688.png

marcus_sommer

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

or

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.

rmadursk
Contributor III
Contributor III
Author

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

rmadursk
Contributor III
Contributor III
Author

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

rmadursk
Contributor III
Contributor III
Author

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

barnabyd
Partner - Creator III
Partner - Creator III

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.

Barnaby Dunn
BI Consultant