Set Analysis with Dates and Nulls

[brettaustin]

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)

[sunny_talwar]

Can you repost with data in your app?

[swuehl · Accepted solution]

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.

See also

Excluding values in Set Analysis

NULL handling in QlikView

[brettaustin]

Thank you!

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

[vishsaggi]

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

Check here: nice explanation by Stefan.

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