59 Replies Latest reply: Aug 19, 2016 2:08 PM by Lauren Mills

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

• ###### 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

• ###### Re: Set Analysis Expression with date range & null

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

• ###### 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

• ###### 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.

• ###### 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

• ###### Re: Set Analysis Expression with date range & null

Returns a "-"

• ###### 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

• ###### Re: Set Analysis Expression with date range & null

No. Maybe if we just try to figure out how to count the null fields first and then plug into the other expression.

I tried ...

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

[Student ID])

and it returned the value of 0. But If we can get this to work then I feel we could get the larger expression to work. Thoughts?

• ###### Re: Set Analysis Expression with date range & null

Maybe try using this:

=NullCount([Dealer Term Date])

Or Even this:

=Count( If( Isnull([Dealer Term Date]) = -1, [Dealer Term Date]))

Any luck?

• ###### Re: Set Analysis Expression with date range & null

Or even better to create a Flag in your script....

IF(LEN(TRIM([Dealer Term Date]))=0 or ISNULL([Dealer Term Date]), 1, 0) as Flag

Now Simply use below in any expression to count empty or null field

COUNT({<Flag = {1}>}[Dealer Term Date])

Then you could do this:

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

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

Flag = {1}
>}

[Student ID])

What happens?

• ###### Re: Set Analysis Expression with date range & null

trying this now.

• ###### Re: Set Analysis Expression with date range & null

I added it to the script and it loaded fine. Then I updated the expression with...

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

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

Flag = {1}
>}

[Student ID])

and it didn't return any value "-"

• ###### Re: Set Analysis Expression with date range & null

=NullCount([Dealer Term Date])

returned a value!!!

Now how do we incorporate this into our set analysis expression?

• ###### Re: Set Analysis Expression with date range & null

Maybe this Expression:

=

NullCount([Dealer Term Date])

+

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

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

[Student ID])

• ###### Re: Set Analysis Expression with date range & null

The problem with that is the + (and) it needs to be (or).

Returned no value

• ###### Re: Set Analysis Expression with date range & null

What was the dimension when NullCount([Dealer Term Date]) returned a value or was this in a text box object it returned a value?

Also what is the chart dimension here?

• ###### Re: Set Analysis Expression with date range & null

No dimension - just a measure within a KPI.

• ###### 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])

• ###### Re: Set Analysis Expression with date range & null

This gives me the value of 0.

• ###### Re: Set Analysis Expression with date range & null

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

<[Dealer Hire Date] = {'<=12/31/2015'},[Dealer Term Date] -= {'*'}>}[Student ID])

• ###### Re: Set Analysis Expression with date range & null

Hi Sunny,

That provides me the count of people who have a Dealer Term Date between 01/01/2015 and 12/31/2015. That expression is not counting the NULLS that have the Dealer Hire Date less than or equal to 12/31/2015.

• ###### Re: Set Analysis Expression with date range & null

Do you have a field such as Dealer Name or Dealer ID which always have a single Dealer Hire Date (or doesn't have anything?)

If you do, you can try this:

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

• ###### Re: Set Analysis Expression with date range & null

I tweaked it a little -

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

I want to make sure I understand correctly what you're asking. I do have a Dealer Name and or a Dealer Code - but the dates are related to the Students who are employee at the Dealer meaning that each dealer would have Dealer Term Dates for certain employees (students) and a Dealer Hire Date for each employee (student).

With my tweak - it did return a value of 176,918.

The number I'm looking for is 376,228

• ###### Re: Set Analysis Expression with date range & null

I could be wrong but I feel like this should be close to it?

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

But the underlined section is broken. Thoughts

• ###### Re: Set Analysis Expression with date range & null

Nope that won't work you cannot list same field twice in set analysis. Let's focus on getting this done through the other option. What is one field that uniquely define Dealer Term Date? DealerID? Each Dealer should have one Term Date, is that true?

• ###### Re: Set Analysis Expression with date range & null

Each Student ID should have either a null or a Dealer Term Date.

• ###### Re: Set Analysis Expression with date range & null

Meaning that they are either still employed or they have a Dealer Term Date (end of employment with dealership).

• ###### Re: Set Analysis Expression with date range & null

So a Student ID will only ever have a single Dealer Term Date. Is this statement true?

• ###### Re: Set Analysis Expression with date range & null

True.

• ###### Re: Set Analysis Expression with date range & null

Try this, I gave you incorrect expression:

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

• ###### Re: Set Analysis Expression with date range & null

This gave me 544,928.

Let me try to explain further.

I want the Beginning Population for the year 2015.

Beginning Population for 2015 = Anyone who was hired [Dealer Hire Date] before and up until 12/31/2015. But also who is still employed (meaning they don't have a [Dealer Term Date] or they have a [Dealer Term Date] between the dates of 01/01/2015 - 12/31/2015.

• ###### Re: Set Analysis Expression with date range & null

That's what it should have been doing, try this one time:

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

• ###### Re: Set Analysis Expression with date range & null

This still returns the value of 544,928

• ###### Re: Set Analysis Expression with date range & null

How about adding a Distinct in there? Would that make a difference?

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

• ###### Re: Set Analysis Expression with date range & null

That gives me 124,024. I don't think we want distinct though. We want to track how often the student jumps position to position within the dealership so they need to be accounted for each time they have a Hire or Term Date.

• ###### Re: Set Analysis Expression with date range & null

Hmmm that make sense, but if that is true, it seems that a particular student ID can have two Term Dates? One with one dealer and another one for another dealer? Is that true?

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

Sorry for flooding your post, but I am trying my best

• ###### Re: Set Analysis Expression with date range & null

Please flood away!!!! Haha! I need help!

This gave me 233,968

• ###### Re: Set Analysis Expression with date range & null

Alright lets break this down into two expressions and see what we get:

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

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

See which of the two expressions are incorrect here

• ###### Re: Set Analysis Expression with date range & null

1) This gives me the total count of Student Ids that have a Dealer Term Date in 2015  (49,080)

2) Gives me the value of 184,888   <- Not sure what this is a real count of.

• ###### Re: Set Analysis Expression with date range & null

Hmmm. Well technically, I guess the Student ID could have multiple Dealer Term Dates with the same or different Dealer. They would each get their own row for each start and end date.

Are you saying I need to be able to bring in another qualifier? I have Position Codes but it's possible they quit as a sales man but came back as a sales man.

• ###### Re: Set Analysis Expression with date range & null

So I guess are we counting a Student ID which left and joined another guy or are we not counting them? In the above case if a student id never had a Dealer Term Date is only time he will be counted in using the Concat() thing.

Student ID     Dealer Term Date

1,

1,      10/20/2016

2,

Here 1 won't be counted, 2 will be. Do we want both 1 and 2 to be counted or just 1 (2nd Student ID

• ###### Re: Set Analysis Expression with date range & null

Student ID    Dealer Term Date

1,                             < We would count them (BUT ONLY IF THEIR HIRE DATE IS BEFORE 12/31/2015

1,      10/20/2016     < would not be counted because the Term Date is in 2016

2,                             < We would count them (BUT ONLY IF THEIR HIRE DATE IS BEFORE 12/31/2015

• ###### Re: Set Analysis Expression with date range & null

But the first two rows are the same Student ID, you will calculate him once? Is that true?

• ###### Re: Set Analysis Expression with date range & null

In regards to the specifics that we are looking at he would get counted - 1 time. He wouldn't get counted a second time because the 3rd statement (having a Dealer Term Date before 12/31/2015) is false.

• ###### Re: Set Analysis Expression with date range & null

We would count every line because the statements are true -

1) they have a hire date before 12/31/2015

2) they have a Dealer Term Date between 1/1/2015 and 12/31/2015

or

3) The Dealer Term Date is = NULL because they haven't ended their employment

• ###### Re: Set Analysis Expression with date range & null

Working on it now

• ###### Re: Set Analysis Expression with date range & null

Here is a smaller of sample of data to work with.

• ###### Re: Set Analysis Expression with date range & null

Example Data =

We want to count every single one of these rows because all statements are true.

They have a Dealer Hire Date before 12/31/2015.

They have a Null Dealer Term Date or a Dealer Term Date between the dates of 01/01/2015 and 12/31/2015.

 Dealer Hire Date Dealer Term Date 10/1/2014 10/2/2014 10/28/2003 2/7/2014 2/7/2014 3/26/2014 9/6/2003 9/12/2003 3/10/2004 2/7/2014 9/17/2013 2/9/2005 2/11/2005 2/7/2014 3/26/2014 5/3/2004 2/7/2014 9/17/2013 2/9/2005 2/11/2005 2/7/2014 3/26/2014 11/24/2011 11/28/2011 2/7/2014 2/7/2014 3/26/2014 9/6/2003 9/12/2003 3/10/2004 2/12/2009 2/7/2014 9/17/2013 1/30/2015 2/9/2005 2/11/2005 2/7/2014 3/26/2014 9/6/2003 9/12/2003 3/10/2004 2/7/2014 9/17/2013 2/9/2005 2/11/2005 2/7/2014 3/26/2014 10/23/2003 2/7/2014 2/7/2014 3/26/2014 9/12/2003 3/10/2004 2/12/2009 4/23/2009 2/7/2014 1/30/2015 2/9/2005 2/11/2005 2/7/2014 3/26/2014 10/28/2003 2/7/2014 2/7/2014 3/26/2014 10/28/2003 2/7/2014 2/7/2014 3/26/2014 10/7/2015 9/6/2003 2/7/2014 2/9/2005 2/11/2005 2/7/2014 3/26/2014 9/6/2003 9/12/2003 3/10/2004 2/12/2009 2/7/2014 1/30/2015 2/9/2005 2/11/2005 2/7/2014 3/26/2014 9/6/2003 9/12/2003 3/10/2004 2/12/2009 2/7/2014 9/17/2013 1/30/2015 2/9/2005 2/11/2005 2/7/2014 3/26/2014 10/28/2003 2/7/2014 2/7/2014 3/26/2014 1/26/2012 10/23/2003 2/7/2014 2/7/2014 3/26/2014 10/23/2003 2/7/2014 2/7/2014 3/26/2014 9/6/2003 9/12/2003 3/10/2004 2/7/2014 9/17/2013 2/9/2005 2/11/2005 2/7/2014 3/26/2014 1/8/2014 1/1/2015 10/24/2015 10/26/2015 10/28/2003
• ###### Re: Set Analysis Expression with date range & null

I will try to create a sample with this data in a little bit

• ###### Re: Set Analysis Expression with date range & null

Here is where I am getting 376,228 from.

Hope it helps! I appreciate it!

• ###### Re: Set Analysis Expression with date range & null

Check the attached

Script:

"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])

• ###### Re: Set Analysis Expression with date range & null

The basic idea here is that you need to get a unique identifier which is associated with a single row. Since Student ID is repeating, I cannot use that, so I created a unique identifier using RowNo() field. You may be able to create a unique field by combining Student ID with Dealer Code in the script which might make Dealer Term Date unique for this new field.

• ###### Re: Set Analysis Expression with date range & null

"Market Code",

"Market Desc",

"District Code",

"District Desc",

"Dealer Code - Active Franchise Only",

"Dealer Name",

"Sales Group Size",

"Student ID",

"First Name",

"Last Name",

"Dealer Hire Date",

"Dealer Term Date",

"Program Year Month (YYYYMM)",

"Position Code",

"Position Name",

"First Name"&' '&"Last Name" as Name

FROM [lib://Retention (cag_t1300lm)/Retention data.xlsx]

(ooxml, embedded labels, table is [Report 1]);

Where and how would I insert? Data load editor didn't like it. Should I create a new page?

• ###### Re: Set Analysis Expression with date range & null

LOAD RowNo() as Key, //-> Option1

AutoNumber("Dealer Name"&"Student ID") as Key2, //->Option2

"Market Code",

"Market Desc",

"District Code",

"District Desc",

"Dealer Code - Active Franchise Only",

"Dealer Name",

"Sales Group Size",

"Student ID",

"First Name",

"Last Name",

"Dealer Hire Date",

"Dealer Term Date",

"Program Year Month (YYYYMM)",

"Position Code",

"Position Name",

"First Name"&' '&"Last Name" as Name

FROM [lib://Retention (cag_t1300lm)/Retention data.xlsx]

(ooxml, embedded labels, table is [Report 1]);

• ###### Re: Set Analysis Expression with date range & null

Sunny T - You've done it again!!!!!!!

You deserve 1,000,000 points!!!

• ###### Re: Set Analysis Expression with date range & null

Super cool.

I am glad we were finally able to get this done

Since this has been an extra long thread, I would suggest marking some of the response as helpful as well as this will help others who land on this page for answers to know what are some of the responses which are helpful. Please do not over do this as well, because it is important that only those response which were helpful are marked to maintain the value of helpful.

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks,

Sunny

• ###### Re: Set Analysis Expression with date range & null

I'm on it! Thanks again as always Sunny T!!!!!!

• ###### Re: Set Analysis Expression with date range & null

Thank you for taking time to mark helpful responses . You can call me Sunny

• ###### Re: Set Analysis Expression with date range & null

Thank you Sunny. You've saved me yet again. It doesn't go unnoticed.