Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
That still gives me 0.
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.
In your script change created date format from 3/15/2011 to 03/15/2011 then try your expression
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)