Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Set Analysis with Dates and Nulls

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)

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Set Analysis with Dates and Nulls

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

View solution in original post

4 Replies
Highlighted

Re: Set Analysis with Dates and Nulls

Can you repost with data in your app?

Highlighted
MVP
MVP

Re: Set Analysis with Dates and Nulls

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

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Set Analysis with Dates and Nulls

Thank you!

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

Highlighted
Champion III
Champion III

Re: Set Analysis with Dates and Nulls

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?