topic Re: Month Function With String in QlikView

Month Function With String

Tue, 07 Jan 2014 17:24:39 GMT

Hello,

Brand new to QlickView and found the Out Of The Box demo to be very helpful getting started. The demo uses the MONTH function to obtain month from a date field but I can't get it to work with a string. During the load process:

Timestamp#(payment_date, 'mm/dd/yyyy hh:mm:ss') as payment_date,
This converts the string to a date field.
year(Timestamp#(payment_date, 'mm/dd/yyyy hh:mm:ss')) as year,
This converts the string to a date field and provides the correct year
month(Timestamp#(payment_date, 'mm/dd/yyyy hh:mm:ss')) as month,
Not sure what this did! My list box only shows 'Jan' when I expected all 12 months.

Can someone explain why I don't get these values in my list box?

Cheers,

-Keith

Re: Month Function With String

swuehl — Tue, 07 Jan 2014 17:37:48 GMT

Take care of the format codes for Month 'MM' vs minutes 'mm'.

month(Timestamp#(payment_date, 'MM/DD/YYYY hh:mm:ss')) as month,

Re: Month Function With String

sureshbaabu — Tue, 07 Jan 2014 17:40:02 GMT

Hello,

can you please try the following : =month(date(floor(date_col)))

Thanks

Re: Month Function With String

Colin-Albert — Tue, 07 Jan 2014 17:46:43 GMT

As swuehl says mm refers to minutes, MM to month!

I always use uppercase for date formats, lowercase for time formats to avoid this confusion.

date(date#(payment_date, 'MM/DD/YYYY hh:mm:ss')) as payment_date,

year(date#(payment_date, 'MM/DD/YYYY hh:mm:ss')) as year,
month(date#(payment_date, 'MM/DD/YYYY hh:mm:ss')) as month

Re: Month Function With String

Tue, 07 Jan 2014 19:26:13 GMT

Is there a big difference between date# and timestamp#?