topic Re: dynamically select yesterday in QlikView

dynamically select yesterday

Anonymous — Mon, 08 Jul 2013 16:21:04 GMT

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,

Re: dynamically select yesterday

Anonymous — Mon, 08 Jul 2013 17:13:36 GMT

Any ideas?

Re: dynamically select yesterday

Anonymous — Mon, 08 Jul 2013 18:00:00 GMT

Vladimir,

Try this:

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

Regards,

Michael

Re: dynamically select yesterday

jagan — Mon, 08 Jul 2013 18:06:37 GMT

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.

Re: dynamically select yesterday

Anonymous — Tue, 09 Jul 2013 15:57:52 GMT

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

Re: dynamically select yesterday

Anonymous — Tue, 09 Jul 2013 16:06:58 GMT

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)

Re: dynamically select yesterday

Anonymous — Tue, 09 Jul 2013 16:20:06 GMT

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,

Re: dynamically select yesterday

Anonymous — Tue, 09 Jul 2013 16:32:09 GMT

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

Re: dynamically select yesterday

Anonymous — Tue, 09 Jul 2013 17:12:52 GMT

I did it. Thanks,

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

Thank you so much,