MonthEnd() Function producing incorrect date in Export

[Anonymous]

We have used the MonthEnd() function in our load script Master Calendar to create a Month End date for our "DataDate". We used this in a Pivot Table chart and it looks correct in Qlik, but converts to the next day in MS Excel. Found out that the reason for this is because Qlik Sense reads 12/31/2015 12:00:00 AM as 12/31/2015 where as Excel reads it as 1/1/2016.

I am sure I could come up with a quick remedy for this, however wanted to know what others thoughts were in this case and other cases they have had experience with.

Thanks!

Clayton

[sunny_talwar · Accepted solution]

Try adding Date and Floor to the MonthEnd, because MonthEnd gives a timestamp value.. for example 12/31/2017 23:59:59

Date(Floor(MonthEnd(....)))

[Anonymous]

Perfect! I was questioning the need for the Floor() function, but found it was necessary and research told me that the MonthEnd() function does some type of Time Stamping which requires the use of the Floor() function. Who knew?....not I.

Thanks for your answer. All is well in my personal shop. Good day!