Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
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
Partner

I would recommend:

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

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

jonathandienst
Partner
Partner

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
Support (Former)
Support (Former)

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.