Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brettaustin
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
swuehl
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
sunny_talwar

Can you repost with data in your app?

swuehl
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

brettaustin
Contributor III
Contributor III
Author

Thank you!

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

vishsaggi
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?