Distinct ClientID in last 45 days

[greghise]

I'm new to set analysis, and I thought I was on the right track with the following statement

What I want is a count of distinct ClientIDs that that our employees have visited in the previous 45 days.

I'm using the expression below but it is returning about twice as many clients as I would find in a direct query of the database.

count({$<VisitDay={">=$(Today()-45)<=$(Today())"}, VisitHrs={">0"}>} distinct ClientID)

Suggestions appreciated.

:G

[treysmithdev]

You will need to add an equal sign to the dollar sign expansion for it to evaluate. You will also need to add a Date() function to the first parameter because it gets changed to a number format after subtracting the 45.

count({$<VisitDay={">=$(=Date(Today()-45))<=$(=Today())"}, VisitHrs={">0"}>} distinct ClientID)

[greghise]

Thanks Trey, unfortunately, that resolved to 0 clients.

When I copied your expression into the expression editor, I did see where the Dollar-Sign Expansion Preview changed from:

count({$<VisitDay={">=<="}, VisitHrs={">0"}>} distinct ClientID

to:

count({$<VisitDay={">=7/29/2019<=9/12/2019"}, VisitHrs={">0"}>} distinct ClientID)

For a moment, I was very excited, but when applied, got a zero count?

[treysmithdev]

What format are the values in VisitDay?

[greghise]

yyyy-mm-dd

[treysmithdev]

You'll need to match that then.

Try: 

count({$<VisitDay={">=$(=Date((Today()-45),'YYYY-MM-DD'))<=$(=Date(Today(),'YYYY-MM-DD'))"}, VisitHrs={">0"}>} distinct ClientID)

[greghise · Accepted solution]

You Sir, are a genius!  Solved!!!!

Thank you!