Date format

[#1]

Hi,

I have a date column which I'm not able to convert to MM/DD/YYYY format. I used Date(LONG_DATE,'MM/DD/YYYY')AS DATE in the LOAD script but getting no data return.

Also, I'm trying to find if any country went from a count in previous month to zero in the next month. Please advise how to construct the expression.

For example Country Mexico filed 2 on 11/30/2013, and filed 0 in 12/31/2013. This is something I want to highlight.

This is the expression I have but it is not working:

Expression: SUM({<METRIC_NAME={'Filed'},Date={'11/30/2013'}>}METRIC_VALUE)-SUM({<METRIC_NAME={'Filed'},Date={'12/31/2013'}>}METRIC_VALUE)

Dimension: Country

Thank you so much for your help!

Jasmine

[datanibbler]

Hi Jasmine,

what form does the date_value take such as you get it directly from your database?

=> Test the DATE#() function, specifying the format your date has (helps if it is not categorized as a "date" value in the database)

=> For that month_over_month figure to calculate, you could then just use the month() function, that way you wouldn't have to always specify the exact dates.

HTH

Best regards,

DataNibbler

[maxgro]

for first question use date# instead of date 

[#4]

Hi DataNibbler,

Thank you for your quick response, I used DATE#() and it works, but it displays the date like 11/30/2013 0:00, Do you know how to remove 0:00?

I get the concept but unable to construct the expression. Also, could you elaborate how to use month() function for the month over month calculation?

Thanks,

Jasmine

[#5]

Try using :

=Date(Floor(Date#(Long_Date,'DD/MM/YYYY hh:mm')))

And What is the format foryour Date field.?

Thanks,

Angad

[#6]

Hi Angad,

I used =Date(Floor(Date#(Long_Date,'DD/MM/YYYY hh:mm'))) and didn't return anything. I want to change the format to MM/DD/YYYY.

Date#(LONG_DATE, 'MM/DD/YYYY') but it came back with 11/30/2013 0:00. I want to eliminate 0:00.

Thank you!

Jasmine

[#7]

use:

date(floor(Date#(LONG_DATE,'MM/DD/YYYY hh:mm')),'MM/DD/YYYY')

[#8]

Somehow it is not working

[#9]

Try Like below:

Date(Floor(Date#(Long_Date,'DD/MM/YYYY')),'YYYY-MM-DD') 

You can change the bold area what format u need.

[#10]

I used the same expression, please see the attached. It worked for me

Thanks,

Angad