Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

59 Replies
sunny_talwar

Working on it now

sunny_talwar

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


sunny_talwar

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.

Not applicable
Author

LOAD

    "Market Code",

    "Market Desc",

    "Business Center Code",

    "Business Center 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?

sunny_talwar

How about like this:

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

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

    "Market Code",

    "Market Desc",

    "Business Center Code",

    "Business Center 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]);

Not applicable
Author

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

You deserve 1,000,000 points!!!

with expression & load script.

sunny_talwar

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

Not applicable
Author

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

sunny_talwar

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

Not applicable
Author

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