topic Help with removing NULL counts in QlikView

Help with removing NULL counts

patrick_forbes — Sat, 16 Nov 2024 02:59:12 GMT

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

Re: Help with removing NULL counts

mostwanted123 — Wed, 17 Jul 2019 04:43:43 GMT

Hi,

Try below code -

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

Thanks

Re: Help with removing NULL counts

mostwanted123 — Wed, 17 Jul 2019 04:45:45 GMT

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

Thanks

Re: Help with removing NULL counts

MindaugasBacius — Wed, 17 Jul 2019 05:20:33 GMT

I would recommend:

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

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

Re: Help with removing NULL counts

jonathandienst — Wed, 17 Jul 2019 05:31:51 GMT

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

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

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

Re: Help with removing NULL counts

Brett_Bleess — Thu, 08 Aug 2019 21:41:04 GMT

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