topic Re: Date format convert to numeric in QlikView

Date format convert to numeric

jim_chan — Mon, 25 Jul 2016 05:26:55 GMT

Hi guys,

i was trying to convert a date(End Date) column to numeric,

this is the expression i wrote down:

num(MonthEnd(MakeDate(Year(Date#(myear,'YYYY')),Month(Date#(mymonth,'MMMM'))))) as EndDate

End Result: 42400.9939292 .

Problem: how to get rid of the .9939292?

Rgds

Jim

avinashelite — Mon, 25 Jul 2016 05:32:34 GMT

Try like this

floor(num(MonthEnd(MakeDate(Year(Date#(myear,'YYYY')),Month(Date#(mymonth,'MMMM')))))) as EndDate

jim_chan — Mon, 25 Jul 2016 05:33:50 GMT

oh... floor......

jim_chan — Mon, 25 Jul 2016 05:36:39 GMT

thanks bro Avin

jonathandienst — Mon, 25 Jul 2016 05:40:37 GMT

The num() is redundant if you are using floor. It is only a format instruction so it makes no sense inside a calculation.

This (much simpler) expression should work:

=Floor(Date#(mYear & mymonth, 'YYYYMMMM'))

jim_chan — Mon, 25 Jul 2016 05:46:52 GMT

Thank Jonathan,

but i need to create a end date field. So, i took out the 'num', now is as below:

floor(MonthEnd(MakeDate(Year(Date#(MyYear,'YYYY')),Month(Date#(MyMonth,'MMMM'))))) as EndDate?

How simpler can it be now ?

Rgds

Jim