topic Re: Makedate not working in QlikView

Makedate not working

gauthamchilled — Tue, 27 Oct 2015 03:14:39 GMT

Anyone help attached excel source and qlikview file, Makedate function is not working.

Re: Makedate not working

sunny_talwar — Tue, 27 Oct 2015 03:21:10 GMT

Try this:

Test:

LOAD Makedate(Year, Month(Date#(Month, 'MMM')), 1) as Date1,

Tarrif,

Ratio,

Month,

Year

FROM

[TestData.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Makedate not working

gauthamchilled — Tue, 27 Oct 2015 03:32:08 GMT

Thanks sunny for the quick answer. I want the date to show monthend dates, so I tried converting to Monthend(Makedate(Year, Month(Date#(Month, 'MMM')), 1)) as Date1, when I checked that listbox of the date field it showing the date as 42095(when I changed date to number in listbox properties) equivalent of this number is april 2015 and not mar 2015. but my current selection is mar 2015. Do you have any idea?

Re: Makedate not working

sunny_talwar — Tue, 27 Oct 2015 03:40:21 GMT

Using the MonthEnd function, I am seeing this:

I also see 42095 when it is converted to number, but I am really not sure why it is doing that. Have you tried checking out the other numbers as well?

Re: Makedate not working

gauthamchilled — Tue, 27 Oct 2015 03:42:56 GMT

I am also seeing that, my question is why if I convert this to number it is not showing equivalent date in excel?

Re: Makedate not working

sunny_talwar — Tue, 27 Oct 2015 03:43:55 GMT

Caught it, Try this:

Test:

LOAD Date(Floor(MonthEnd(Makedate(Year, Month(Date#(Month, 'MMM')), 1)))) as Date1,

Tarrif,

Ratio,

Month,

Year

FROM

[TestData.xlsx]

(ooxml, embedded labels, table is Sheet1);

Floor will do the magic:

Re: Makedate not working

jagan — Tue, 27 Oct 2015 03:50:32 GMT

Hi Gautham,

MonthEnd() for this date will return 42094.999999988, which is equal to March 31, 11:59:59 PM, so when displaying as number it is rounding off to 42095, if you display this value in List box as Fixed to 9 decimals then you can see 42094.999999988.

Hope this helps you.

Regards,

Jagan.