Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Distinct ClientID in last 45 days

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

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Contributor II
Contributor II

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

Thank you!

View solution in original post

6 Replies
Highlighted
Luminary
Luminary

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)
Blog: WhereClause   Twitter: @treysmithdev
Highlighted
Contributor II
Contributor II

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?

Highlighted
Luminary
Luminary

What format are the values in VisitDay?

Blog: WhereClause   Twitter: @treysmithdev
Highlighted
Contributor II
Contributor II

yyyy-mm-dd

Highlighted
Luminary
Luminary

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)

 

Blog: WhereClause   Twitter: @treysmithdev
Highlighted
Contributor II
Contributor II

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

Thank you!

View solution in original post