topic Set Analysis Troubleshooting - Multiple Conditions with Date in App Development

Set Analysis Troubleshooting - Multiple Conditions with Date

acbishop2 — Sat, 16 Nov 2024 21:05:20 GMT

Hi All,

A simplified version of my data is as follows:

LeadId	CreatedDate	Source	LeadValue
12345	3/11/2019	ps	1
12346	3/12/2019		1
12347	3/13/2019		1
12348	3/13/2019		1
12349	3/13/2019
12350	3/19/2019	ps	1
12351	3/19/2019	ps	1
12352	3/20/2019	ps
12353	3/20/2019		1
12354	3/21/2019	ps	1

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.

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

dplr-rn — Tue, 09 Apr 2019 16:04:01 GMT

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)

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

acbishop2 — Tue, 09 Apr 2019 16:08:05 GMT

That still gives me 0.

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

dplr-rn — Tue, 09 Apr 2019 16:10:53 GMT

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

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

dwforest — Tue, 09 Apr 2019 16:13:46 GMT

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)

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

acbishop2 — Tue, 09 Apr 2019 16:54:11 GMT

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.

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

shraddhawalekar — Thu, 18 Apr 2019 05:12:13 GMT

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

Re: Set Analysis Troubleshooting - Multiple Conditions with Date

shraddhawalekar — Thu, 18 Apr 2019 05:14:49 GMT

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)