Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ndeeleysww
Contributor

Today() variable and time stamp issue

Hi there,

I'm pulling date from Access to create a bar chart.

I have created a variable in my load script which pulls back today:

LET vTodayTY = Today();

...so I can pull out all completed records from the start of the year to this date in the measure:

sum({$<ChargeableYN={'1'},CompletionDate={">=$(#vFirstDayThisFinancialYear)<=$(#vTodayTY)"}>} [AmountCharged])

My Completion Date is pulled out of the Access DB like this:

date(CompletionDate ,'DD/MM/YYYY') as CompletionDate

I have one record with a CompletionDate of 08/01/2015 10:35:42. This record is not included in my bar chart using the start of the year and the Today() variable. If I change the CompletionDate to the 07/01/2015 it is included.

Is there a timestamp I'm not removing that is causing this record not to be used as part of the calculation?

Cheers

Neil

Tags (3)
1 Solution

Accepted Solutions
danielact
Contributor III

Re: Today() variable and time stamp issue

You can either use Now() instead of today(), or you can use DayStart on your data field like this:

date(DayStart(CompletionDate) ,'DD/MM/YYYY') as CompletionDate

That will change all the timestamps to 00:00:00 so they'll be the same as the Today() function.

4 Replies
Not applicable

Re: Today() variable and time stamp issue

Hi,

Try using the Now() function instead of the Today() function:

LET vTodayTY = Now();

MVP
MVP

Re: Today() variable and time stamp issue

Hi

Today() returns a time of 00:00:00 for today's date, so any transaction happening today will have a timestamp > today(). You can either remove the time component from the CompletionDate during load, or else change your definition of vTodayTY to include a time component:

LET vTodayTY = TimeStamp(Today() + (1 - 1/86400));  // today at 23:59:59

HTH

Jonathan

danielact
Contributor III

Re: Today() variable and time stamp issue

You can either use Now() instead of today(), or you can use DayStart on your data field like this:

date(DayStart(CompletionDate) ,'DD/MM/YYYY') as CompletionDate

That will change all the timestamps to 00:00:00 so they'll be the same as the Today() function.

ndeeleysww
Contributor

Re: Today() variable and time stamp issue

Brilliant - thank you Jose, Jon and Dan for your replies. I've gone with Dan's as it removes all the ambiguity when I load in the data from the date fields.

Cheers

Neil