Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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
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!!!!