Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Date-Time formatting

Hi all,

I am importing data that includes date-time data, as follows:

Qualify *;

[MALN]:

LOAD  [submissiondate],

[start]

...

[KEY]

FROM ...

[start] is a date-time with formatting 'DD/MM/YYYY  hh:mm:ss'

I sense that QLIK Sense "sort of" recognizes this as a date, but not totally.

I want to make a bar chart with this data, with the date along the x-axis (as the dimension), and the count of [KEY] as the measure.

So I put the expression: =date(daystart([MALN.start],'DD/MM/YY')) for the x-axis.

And it just gives me a null value for date "-" - so all entries in the table show up counted for "-" date.

I've tried everything I could find in the QLIK Community for bringing the [start] in as a date (i.e., date#(start, 'DD/MM/YYYY  hh:mm:ss')), but I am at a loss.  If anyone can help out at all, or if you have any ideas, I would be so appreciative.

Many thanks,

Janna

1 Solution

Accepted Solutions
MVP
MVP

Re: Date-Time formatting

Excel is probably changing the format of the dates based on ts own date recognition. For the CSV file, you need to check the original date format using a text editor (notepad will do if you have nothing else) and change your interpretation to match that format.

14 Replies
MVP
MVP

Re: Date-Time formatting

Try this:

=Date(DayStart([MALN.start]),'DD/MM/YY')

or

=Date(Floor([MALN.start]), 'DD/MM/YY')

Not applicable

Re: Date-Time formatting

Hi Sunny,

Thanks for the correction. 

Here's the thing - I'm pulling the data in from a .csv file. 

When I import the data into a .xls file and then import the .xls into QLIK, Sense is able to read the date fine using that formula (as normal, I've done this hundreds of times)...

But QLIK still won't seem to recognize the date when it pulls it from the raw .csv file.

Any suggestions?

Thanks again,

Janna

Re: Date-Time formatting

Try this as calculated dimension

=Daystart(Date([MALN.start],'DD/MM/YY'))

1. First convert to date format

2. Then use DayStart for the finding Daystart

Regards

Anand

MVP
MVP

Re: Date-Time formatting

You can use Date#(), TimeStamp#() Time#() function to help QlikSense interpret your datefields. (Why don’t my dates work?)

reddys310
Honored Contributor II

Re: Date-Time formatting

Hi Janna,

Did you check if Qlik is interpreting the field as a date field? If so this has to addressed first.

Thanks,

Sangram.

Not applicable

Re: Date-Time formatting

Hi Sunny,

I tried to use interpretation for my dates, but it doesn't work at all.  It's really strange, because if I import the .csv to .xls then it works.  Is there a reason it might not work as .csv dates??

Thanks,

Janna

MVP
MVP

Re: Date-Time formatting

Can you share a row from your csv in the csv format so that we can test it out?

MVP
MVP

Re: Date-Time formatting

HI,

Try like this

Date(Floor(DateFieldName),  'DD/MM/YYYY')


OR


Date(Floor(TimeStamp#(DateFieldName, 'DD/MM/YYYY  hh:mm:ss')), 'DD/MM/YYYY')


Hope this helps you.


Regards,

Jagan.

brijesh1991
Contributor III

Re: Date-Time formatting

SET DateFormat='DD/MM/YYYY'; // You can set whatever format you want-date or timestamp

Load * ,
Date(MakeDate(Year([start]),Month([start]),Day([start]),'$(DateFormat)') as StartModifiedDate

From Table...........