Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
patrick_forbes
Contributor II
Contributor II

Help with removing NULL counts

Hello, I don't know if this requires a total rewrite but having difficulty getting the count of AD_Account_Name to not include NULL's from the below. Does anyone have any tips?

Count (distinct (if([AD_LastLogonTimestamp.autoCalendar.Date] < Today()-90,AD_Account_Name)))

Thanks

Patrick

Labels (1)
5 Replies
mostwanted123
Creator
Creator

Hi,

Try below code -

IF(not isnull(AD_Account_Name), Count (distinct (if([AD_LastLogonTimestamp.autoCalendar.Date] < Today()-90,AD_Account_Name))))

Thanks

mostwanted123
Creator
Creator

OR 

Count (distinct (if([AD_LastLogonTimestamp.autoCalendar.Date] < Today()-90 and not isnull(AD_Account_Name) ,AD_Account_Name)))

Thanks

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

I would recommend:

=Count(DISTINCT
{<
AD_Account_Name = {"<>"}

,[AD_LastLogonTimestamp.autoCalendar.Date]={"<=$(=Date(Today()-90)"}
>}
AD_Account_Name)

jonathandienst
Partner - Champion III
Partner - Champion III

Nomrally, count() will ignore nulls. Your expression has an unusual structure. Move the parentheses and the distinct:

Count (distinct (if([AD_LastLogonTimestamp.autoCalendar.Date] < Today()-90,AD_Account_Name)))

to

Count (If(AD_LastLogonTimestamp.autoCalendar.Date < Today()-90, distinct AD_Account_Name))

or

Count ({<AD_LastLogonTimestamp.autoCalendar.Date = {"<$(=Date(Today()-90))"}>} distinct AD_Account_Name))

 

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Brett_Bleess
Former Employee
Former Employee

Hey Patrick, did any of the replies help you get things sorted, if so, be sure to use the Accept as Solution button to give any of them credit that did help.  If you are still trying to figure things out, leave a new post.  

Only additional thing I have is a Design Blog post that might help:

https://community.qlik.com/t5/Qlik-Design-Blog/Finding-NULL/ba-p/1474279

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.