Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

delbooth
New Contributor II

Date and time from excel

I have created a 2 master calendars from the same data source as they refer to different stages of the process ie order entry and collection dates.

My question is that when I use the below expression:

Sum({<ColYear={'2018'},ColWeek = {"$(=Week(Today())-1)"}>}[Quantity Collected])

ColYear and Col]Week refer to the dates in the collection master calendar and gives the correct values.  i then created

Sum({<EntYear={'2018'},EntWeek = {"$(=Week(Today())-1)"}>}[Quantity Collected]) whci refers to the second master calendar.  This does not seem to work and the question  -

Does the date format matter ie for 1st master calendar the date formats are all 27/07/2017 00:00 and the second is 18/04/2017 14:50 in that it has time value as well not 00:00.

Do I need to set the 2nd calendar dates to have time value of 00:00

Thanks

del

10 Replies
MVP
MVP

Re: Date and time from excel

It will depend on your calendar script, but in general it is better to create a master calendar without the time component

delbooth
New Contributor II

Re: Date and time from excel

How do I do that I am loading the data using the data load editor, do I set the it to ignore times when creating the master calendar.

Thanks

MVP
MVP

Re: Date and time from excel

May be create a new field just for creating the calendar

LOAD  OrderDate as OrderDateandTime,

    Date(Floor(OrderDate)) as OrderDate

Floor will remove Time and you can now use OrderDate to create your calendar

sureshqv
Esteemed Contributor III

Re: Date and time from excel

try this

Date(Trim(floor(Date("OrderDate",'Dateformat'))),'MM/DD/YYYY') as [OrderDate],

MVP
MVP

Re: Date and time from excel

Chanty bhai 2-4 aur expressions functions bhi laga dalo...

sureshqv
Esteemed Contributor III

Re: Date and time from excel

haha let him try all 

MVP
MVP

Re: Date and time from excel

But why would you use Trim() after Floor()

delbooth
New Contributor II

Re: Date and time from excel

Thanks for all the help

omarbensalem
Esteemed Contributor

Re: Date and time from excel

If u create a master calendar and its Date field format would be DD/MM/YYYY; don't expect it to join correctly with ur data if the time part exists.

U'll have to eliminate the time part of ur second Date, maybe using floor()