Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Set Analysis Expression with date range & null

Hello,

What's wrong with my expression?

Count({<[Dealer Hire Date] = {'<=12/31/2015'},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')" or null}>}[Student ID])

I am trying to count Student ID where their Dealer Hire Date is Less Than or Equal to 12/31/2015 AND they either have a Dealer Term Date in-between the dates of 01/01/2015 - 12/31/2015 OR they don't have a Dealer Term Date.

My KPI object keeps returning the value of 0. I have a feeling my problem fall within how I am writing in the expression 'OR NULL'.

Suggestions on how to write this?

1 Solution

Accepted Solutions

Re: Set Analysis Expression with date range & null

Check the attached

Script:

LOAD RowNo() as Key,

    "Student ID",

    "Dealer Hire Date",

    "Dealer Term Date"

FROM [lib://Lib/SunnyT2.xlsx]

(ooxml, embedded labels, table is Sheet1);


Expression:

Count({<[Dealer Hire Date] = {"$(='<=' & '12/31/2015')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')"}>+

    <[Dealer Hire Date] = {"$(='<=' & '12/31/2015')"}, Key = {"=Len(Trim([Dealer Term Date])) = 0"}>}[Student ID])


59 Replies
miguelbraga
Valued Contributor III

Re: Set Analysis Expression with date range & null

Try this:

Count({<
[Dealer Hire Date] = {'<=12/31/2015'},
[Dealer Term Date] =
     p(
          1{
          [Dealer Hire Date] = {'<=12/31/2015'},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')")}

            } [Dealer Hire Date]

)
>}

[Student ID])

Hope it helps

Regards,

MB

PS: this function was made using the help of the document provided by this post:

Set Analysis: syntaxes, examples

Not applicable

Re: Set Analysis Expression with date range & null

That didn't work. It returned a "-" in my KPI. Thanks for trying.

miguelbraga
Valued Contributor III

Re: Set Analysis Expression with date range & null

What is the range of dates you're trying to achieve in Dealer Hire Date?

I also think that the problem is the use of "or null" in Set Analysis expression. Let me search a bit more

miguelbraga
Valued Contributor III

Re: Set Analysis Expression with date range & null

Have you tried simply with this expression?

Count({<[Dealer Hire Date] = {'<=12/31/2015'},[Dealer Term Date] = {"1(= '>=' & '01/01/2015' & '<=' & '12/31/2015')"}>}[Student ID])

Not applicable

Re: Set Analysis Expression with date range & null

This gives me the value of 0.

Not applicable

Re: Set Analysis Expression with date range & null

I want the count of students who were hired before 12/31/2015.

And who are currently still employed (null for Dealer Term Date) or have a Dealer Term Date between 01/01/2015 and 12/31/2015.

I'm trying to get the beginning population for the year of 2015.

miguelbraga
Valued Contributor III

Re: Set Analysis Expression with date range & null

Give this a try:

Count({<
[Dealer Hire Date] = {'<=12/31/2015'},

[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')")} + {''}
>}

[Student ID])

Regards,

MB

Not applicable

Re: Set Analysis Expression with date range & null

Returns a "-"

miguelbraga
Valued Contributor III

Re: Set Analysis Expression with date range & null

Maybe this:

Count({<
[Dealer Hire Date] = {'<=12/31/2015'},

[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')")},

[Dealer Term Date] = {"$(=null())"}
>}

[Student ID])

Regards,

MB