Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
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

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

Can you repost with data in your app?

Highlighted
MVP
MVP

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

Contributor III
Contributor III

Thank you!

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

Highlighted
Champion III
Champion III

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?