Set Analysis with date comparison

[REF123]

Hello,

I have not been able to figure out how to compare two dates in set analysis. My goal is to show the count of email addresses where the user has logged in in the last 30 days. My set analysis expression has three components- IsActive=1, EmailAddress is not blank and the date check. The first two parts work fine. The date check seems to be completely ignored. I most recently moved both dates into variables for the sake of the date formatting code. I had initially included the formatting code in the set analysis.

I have tried so many different variations of the code but with no luck on the date comparison. Help would be greatly appreciated!

Thanks!

My latest expression is as follows:

count({<[User.IsActive] = {1}, [User.EmailAddress] = {"=len([User.EmailAddress])>0"}, varLoginDate ={">=$varTodayMinus30"} >} Distinct User.EmailAddress)

varLoginDate is defined as Date([User.LastLoginTime],'YYYY-MM-DD')

varTodayMinus30 is defined as Date(Today()-30,'YYYY-MM-DD')

[BrunPierre]

Have you already tried this?

[User.LastLoginTime]= {">=$(=Date(Today()-30,'YYYY-MM-DD')))"} >}

[REF123]

Thanks for looking at this. I did try that initially and it produces a count of 0 no matter what (if I readjust to -60, -100 or anything). That's when I thought that the date formats might need to match exactly in case that was the issue. LastLoginTime is datetime. I wasn't able to format LastLoginTime in the expression without getting an error so I tried moving one or both dates to variables. I even tried master dimensions. No luck.

[BrunPierre]

Post some values of the LastLoginTime field.

[REF123]

LastLoginTime samples:

2021-07-01 19:53:29.600000

2023-03-06 18:29:36.987000

2023-03-21 15:02:27.797000

[BrunPierre · Accepted solution]

You would want to do this at the back end.

Date(Floor(Timestamp#(LastLoginTime ,'YYYY-MM-DD hh:mm:ss.FFFFFF')),'YYYY-MM-DD') as LastLoginTime;

[REF123]

I tried changing to this format in the variable varLoginDate and it still ignores the date comparison. Perhaps I need to format the field on the back end in the data load editor. I'm not sure why it won't work in the variable or allow the formatting in the set analysis expression.

[BrunPierre]

Now that there's conformity with the date format, try again with this.

---

[User.LastLoginTime]= {">=$(=Date(Today()-30,'YYYY-MM-DD')))"}

[BrunPierre]

If you still want to use variable, maybe like this.

varTodayMinus30 = Date(Max([User.LastLoginTime])-30,'YYYY-MM-DD')

count({<[User.IsActive] = {1}, [User.EmailAddress] = {"=len([User.EmailAddress])>0"}, [User.LastLoginTime] ={'>=$(=varTodayMinus30 )'} >} Distinct User.EmailAddress)

[REF123]

This works perfectly when I format with your code in the back end in the data load editor. Thank you! I haven't been able to get it to work using a variable but can look more at that later or just use the back end.