Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
acbishop2
Contributor

Set Analysis Troubleshooting - Multiple Conditions with Date

Hi All,

A simplified version of my data is as follows:

LeadIdCreatedDateSourceLeadValue
123453/11/2019ps1
123463/12/2019 1
123473/13/2019 1
123483/13/2019 1
123493/13/2019  
123503/19/2019ps1
123513/19/2019ps1
123523/20/2019ps 
123533/20/2019 1
123543/21/2019ps1

 

I'm trying to use set analysis to add the LeadValue of all leads that have a Source of ps and a CreatedDate on or after 3/15/2019. Here's what I've tried:

Sum({$<Source={"ps"},CreatedDate={">=03/15/2019"}>}LeadValue)

This returns 0, which is weird because it's what I've found on other threads. I've also tried using single quotes around the date: 

Sum({$<Source={"ps"},CreatedDate={">='03/15/2019'"}>}LeadValue)

This doesn't seem to do anything.

Sum({1<Source={"ps"}>+<CreatedDate={">=03/15/2019"}>}LeadValue)

This returns 329, which is the sum of all  LeadValues for leads with Source = "ps", but without respect to the date. 

I assume I'm missing something simple. Thanks in advance for the help.

7 Replies
Partner
Partner

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

most likely cause if because You are trying a >= operation between a date and text.
change to
Sum({$<Source={"ps"},CreatedDate={">=$(=Date#('03/15/2019','MM/DD/YYYY'))"}>}LeadValue)
acbishop2
Contributor

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

That still gives me 0.

Partner
Partner

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

check the type of your createddate column . it should be date
also try makedate function instead of the date#
dwforest
Valued Contributor

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

What is your DateFormat variable set to? if its not MM/DD/YYYY, then 03/15/2019 will not be recognized by Qlik as a Date.
Either change DateFormat or when loading your date data, Date(CreateDate,'MM/DD/YYY') as CreateDate, then :
Sum({$<Source={"ps"},CreatedDate={">='03/15/2019'"}>} LeadValue)
acbishop2
Contributor

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

Ok, here's what I've tried:

1. The CreatedDate field is a DateTime/TimeStamp field, which I thought would be acceptable for the formula. In the load script, I changed "CreatedDate" to "Date(CreatedDate) AS CreatedDate", and that made it so the following gives me 329 instead of 0 (329 is the total not restricted by date):

Sum({$<Source={"ps"},CreatedDate={">=$(=Date#('03/15/2019','MM/DD/YYYY'))"}>}LeadValue)

2. The MakeDate didn't do anything before this change (Date(CreatedDate)), but gives the same result as the above after:

Sum({$<Source={"ps"},CreatedDate={">=$(=MakeDate(2019,3,15))"}>}LeadValue)

3. The DateFormat in the script was set to M/D/YYYY. I changed it to MM/DD/YYYY at the same time I changed "CreatedDate" to "Date(CreatedDate) AS CreatedDate."

So those changes may have helped, but the sums are still not being filtered by CreatedDate.

Partner
Partner

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

In your script change created date format from 3/15/2011  to  03/15/2011 then try your expression

Partner
Partner

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

In your script change the format for CreatedDate from 3/15/2019 to 03/15/2019 and then use youe expression.

Sum({$<Source={"ps"},CreatedDate={">='03/15/2019'"}>} LeadValue)