Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I think you where almost there in one of your attempts. Try this:
Count({$<[Attested_Date] = {"*"}>} DISTINCT([Service]))
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.
Try this:
Count({<Attested_Date={"=len(Attested_Date)>1"}>}Distinct Service)
Nope, same thing. I'm really starting to wonder if there isn't something funny in the data that I can't see.
Thanks,
I think you where almost there in one of your attempts. Try this:
Count({$<[Attested_Date] = {"*"}>} DISTINCT([Service]))
Count({$<[Attested_Date] = {"=Len(Trim([Attested_Date])) > 1"}>} DISTINCT([Service]))
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.
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
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
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
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.