Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
musketeers
Creator
Creator

Date and Datetime issues

Hi,

I‌ have a table field which data type is DateTime. Till last week it has only DATE data like 2017-01-01 00:00:00.

A‌ll data in this field was like this. I was pulling this information in Qlikview using Date function to get only Date part and leaving the time protion.

F‌rom current week, this field in SQL table is also storing HOUR information. So now the data is like 2017-01-01 12:00:00.

B‌ut in qlikview I am still using Date function to get only Date part and leaving the time portion.

M‌y Date function is like: DATE(START_DATE, "DD-MM-YY")

N‌‌ow my question is:

After the hour part is added in SQL Table, Qlikview is not displaying the correct result in charts.

W‌hat I found is that for a particular product in SQL Table, we have data for 2nd July 2017 to 6th JULY 2017

B‌ut Qlikview is displaying data for only 3 July, 4 July and 5 July in chart , but it has loaded all rows from SQL table.

I‌ am not able to understand why Qlikview is not taking data for 2 July and 6 July while calculating data in chart.

I‌ have checked the log file and it says that Qlikview has loaded all rows from SQL table.

P‌lease help me understand why this behaviour

1 Solution

Accepted Solutions
stigchel
Partner
Partner

Using the Date function you don't 'leave' the time portion, it is a formatting function only, please read

The Date Function

View solution in original post

5 Replies
pipuindia99
Creator III
Creator III

Hi Try to use Date# and date like Date(Date#(fieldname,'format'),'format')

stigchel
Partner
Partner

Using the Date function you don't 'leave' the time portion, it is a formatting function only, please read

The Date Function

View solution in original post

Kush
MVP
MVP

Set the timestamp variable format as per the format of START_TIME

SET TimestampFormat='YYYY-MM-DD hh:mm:ss';


Now you can try this


LOAD ..

date(floor(START_DATE),'DD-MM-YY') as START_DATE

...

musketeers
Creator
Creator
Author

Thanks everyone for help.

I have read this article and found the internal working of Date function.

Now I got, even when I was using the Date function to eliminate the Time part from date field, the internal calculation of Qlikview was still using the time part but the front part was displaying only Date part.

Thanks for this useful link.

jasmeenkr
Contributor
Contributor

I am getting error while using expression Date(Datetime,'YYYYMMDD') where DateTime =9/12/2019 12:00:00 AM.

 

It gives me number 43725 . I want to display 20190912.