Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Discover how organizations are unlocking new revenue streams: Watch here
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

dynamically select yesterday

Hi all,

Please help me with my expression.

I want to calculate count for yesterday.

count({<submitDateTime={"=$(=date(today()-1))"}>}respondentID)

it shows me count for whole period.

Thanks,

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Vladimir,

Try this:

count({<submitDateTime={"$(=date(today()-1))"}>} distinct respondentID)

Regards,

Michael

View solution in original post

8 Replies
Anonymous
Not applicable
Author

Any ideas?

Anonymous
Not applicable
Author

Vladimir,

Try this:

count({<submitDateTime={"$(=date(today()-1))"}>} distinct respondentID)

Regards,

Michael

jagan
Partner - Champion III
Partner - Champion III

Hi,

Please check the date format in both Dimension and Date(today()-1), I think they both are in different format

Hope this helps yo.

Regards,

Jagan.

Anonymous
Not applicable
Author

Thanks guys for your help. I had extra = sign.

I have another question

if I want to select between 2 dates.

For example

count({<submitDateTime={">=$(=Date(WeekStart(date(today()-7))))","<=$(=Date(WeekEnd(date(today()-7))))"}>}respondentID)

Thanks,

Vladimir

Anonymous
Not applicable
Author

Usually I use something like this:

count({<submitDateTime={"$(='>=' & date(<exression for min date>) & '<=' & date(expression for max date))"}>}respondentID)

So, it may be:

count({<submitDateTime={"$(='>=' & date(weekstart(today()-7)) & '<=' & date(weekend(today()-7)))"}>}respondentID)

Anonymous
Not applicable
Author

Hi Michael,

It works, but it counts from July 1st -July 7th. But when I check the result, it looks like from July 1-July 6.

I checked weekstart and weekend, it's July 1 and July 7

regards,

Anonymous
Not applicable
Author

I guess that the reason is that your field submitDateTime is a timesatmp, date and hours (minuts, etc.)  The date(weekend()) returns only date, so you get July 7 at midnight, the beginning of the day - all timesatmps during that day are filtered out because theay are not '<='.  You need to change to:

date(weekend(today()-7)+1),

and use '<' instead of '<=' here to exclude anything exactly at midnight.

Regards,

Michael

Anonymous
Not applicable
Author

I did it. Thanks,

I create new variable date(floor(submitDateTime)) as oDate, and changed the expression. It works well.

Thank you so much,