Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

patrick_forbes
New 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
Contributor

Re: Help with removing NULL counts

Hi,

Try below code -

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

Thanks

mostwanted123
Contributor

Re: Help with removing NULL counts

OR 

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

Thanks

MindaugasBacius
Valued Contributor III

Re: Help with removing NULL counts

I would recommend:

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

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

Highlighted
MVP
MVP

Re: Help with removing NULL counts

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
Support
Support

Re: Help with removing NULL counts

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 don't forget to mark a correct resolution or answer to your problem or question as correct.