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: 
has75042
Creator
Creator

Joining dates

Hey All,

I need to find out how many orders were place FY 2019. We have a calendar dim, but somehow I can't connect my source table to calendar dim.

I have changed the table date format to match the calendar dim format, but I still can't get FY2019 field to work.

Source Table Date Format :

4/1/2018 12:00:00 AM

Calendar Dim Date Format:

2/2/2012

the below expression I am using to change the date format

date( `time_res`, 'MM/DD/YYYY') as time_res,

Any help would be really appreciated

Thanks,

1 Solution

Accepted Solutions
pradosh_thakur
Master II
Master II

Hi

the first thing you need to check is your time stamp and date are in date/time format or string?

if they are in timestamp/date format then

for source table date(floor(timestamp_field)) will do

if they are in string then you need to first change them to timestamp then change it to date

date(floor(timestamp#(timestamp_field,'MM/DD/YYYY hh:mm:ss tt')))

In the above i have assumed your dates are in MM/DD/YYYY format, you may nee to change that if otherwise.

 

Thanks

Pradosh

Learning never stops.

View solution in original post

3 Replies
tincholiver
Creator III
Creator III

Hey boddy, try this:

Date(Floor(Date#(date, 'D/M/YYYY h:m:ss')), 'DD/MM/YYYY') as time_res;
pradosh_thakur
Master II
Master II

Hi

the first thing you need to check is your time stamp and date are in date/time format or string?

if they are in timestamp/date format then

for source table date(floor(timestamp_field)) will do

if they are in string then you need to first change them to timestamp then change it to date

date(floor(timestamp#(timestamp_field,'MM/DD/YYYY hh:mm:ss tt')))

In the above i have assumed your dates are in MM/DD/YYYY format, you may nee to change that if otherwise.

 

Thanks

Pradosh

Learning never stops.
Marcos_rv
Creator II
Creator II


yep, as it says tincholiver, you have to use the function floor to make join between dates.

This is because in qv the dates are a number and the date format is a mask, for example:

date (40032.5123, 'dd-mm-yyyy hh: mm: ss')
= 07-17-2009 12:17:42

The numbers that are after the period (.) Represent the hours, minutes and seconds.

So if you had to relate 2 formats, only with the function date could not, since it is a mask, you would have to extract the decimals.

example: date (floor (40032.5123)) = 07-17-2009

regards!!!!