Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

14 Replies
sunny_talwar

Try this:

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

or

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

Not applicable
Author

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

its_anandrjs

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

sunny_talwar

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

reddy-s
Master II
Master II

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
Author

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

sunny_talwar

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

jagan
Luminary Alumni
Luminary Alumni

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
Partner - Specialist
Partner - Specialist

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...........