topic Set Analysis with Dates and Nulls in QlikView

Set Analysis with Dates and Nulls

brettaustin — Wed, 25 Nov 2020 16:16:04 GMT

Hello Community,

I am trying to simply count the number of records where one date field is Null and another is NOT Null. I am getting '0' for my result set. See attached for data example.

=Count (DISTINCT{$<

State = {"Y"},

Date = {"$(=Date(max([Date])))"},

StartDate ={"'-'"},

ApprovalDate -= {"'-'"}

ID)

Re: Set Analysis with Dates and Nulls

sunny_talwar — Wed, 22 Aug 2018 19:43:52 GMT

Can you repost with data in your app?

Re: Set Analysis with Dates and Nulls

swuehl — Wed, 22 Aug 2018 20:49:37 GMT

If you are talking about NULL and not a '-' value, then you can't select or exclude NULL directly in set analysis.

Try maybe something like

=Count (DISTINCT{$<

State = {"Y"},

Date = {"$(=Date(max([Date])))"},

ID = e({< StartDate = {"*"}>}) ,

ApprovalDate = {"*"}

ID)

The search "*" is selecting all symbols.

Re: Set Analysis with Dates and Nulls

brettaustin — Thu, 23 Aug 2018 01:27:42 GMT

Thank you!

Just curious... what does the 'e' in 'ID = e({< StartDate = {"*"}>}) ' represent? Existence?

Re: Set Analysis with Dates and Nulls

vishsaggi — Thu, 23 Aug 2018 02:22:19 GMT

E() is a function that excludes non null values of start date from ID.

Check here: nice explanation by Stefan.

P() & E() and where do you use them?