topic Re: Set Analysis with missing dates in App Development

Set Analysis with missing dates

PamelaAlcantara — Fri, 05 Jun 2020 17:18:33 GMT

Hi All,

I am trying to get the count of the items where the open date is not missing and the Close date is missing.

I have tried the following

Count({<FIRST_OPEN_HSC_DATE={"*"},FIRST_CLOSE_HSC_DATE-={"*"}>}FIRST_CLOSE_HSC_DATE)

and also

Count({<FIRST_OPEN_HSC_DATE={"*"},FIRST_CLOSE_HSC_DATE=e({<FIRST_CLOSE_HSC_DATE= {'*'}>})>}FIRST_OPEN_HSC_DATE)

I don't have a problem with this first part (Open Date), but I can not get the right count of items where the close date is missing.

How can I count missing values on a set analysis?

Thank you so much

Pamela

Taoufiq_Zarra — Fri, 05 Jun 2020 17:40:57 GMT

May be :

=count({<[Open Date]={"=len([Open Date]) > 0"},[Close date]={"=len([Close date]) = 0"}>} Item)

output:

PamelaAlcantara — Fri, 05 Jun 2020 19:26:52 GMT

Unfortunately, I still can not get right count every time I tried to apply the formula

[Close date]={"=len([Close date]) = 0"}

I get no values, if I change the formula to

[Close date]={"len([close date])>0") I get the right classification and count, but with =0 I get nothing.

Taoufiq_Zarra — Fri, 05 Jun 2020 19:49:11 GMT

can you share what's not working?

Attached the qliksense file

PamelaAlcantara — Fri, 05 Jun 2020 21:17:35 GMT

I was finally able to solve it, the trick was to add 1-& before the second expression

Count({<Open Date={"=len([Open Date])>0"}>}{1-$<Close Date={"=len([Close Date])>0"}>}[Item]),

Thank you so much!