Makedate not working

[gauthamchilled]

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

[sunny_talwar]

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);

[gauthamchilled]

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?

[sunny_talwar]

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?

[gauthamchilled]

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

[sunny_talwar · Accepted solution]

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:

[jagan]

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.