topic Re: Date format in QlikView

Date format

Fri, 14 Feb 2014 15:36:25 GMT

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

Re: Date format

datanibbler — Fri, 14 Feb 2014 15:42:09 GMT

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

Re: Date format

maxgro — Fri, 14 Feb 2014 15:42:32 GMT

for first question use date# instead of date

Re: Date format

Fri, 14 Feb 2014 15:50:34 GMT

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

Re: Date format

Fri, 14 Feb 2014 16:09:21 GMT

Try using :

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

And What is the format foryour Date field.?

Thanks,

Angad

Re: Date format

Fri, 14 Feb 2014 16:16:40 GMT

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

Re: Date format

Fri, 14 Feb 2014 16:26:00 GMT

use:

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

Re: Date format

Fri, 14 Feb 2014 16:32:49 GMT

Somehow it is not working

Re: Date format

Fri, 14 Feb 2014 16:49:14 GMT

Try Like below:

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

You can change the bold area what format u need.

Re: Re: Date format

Sat, 15 Feb 2014 06:01:04 GMT

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

Thanks,

Angad