Add a flag in script to identify last day of the month

[guillaume_gorli]

Dear all,

i would like to add a flag in my script so that i could easily trigger the last day of any calendar month in set analysis.

Can anyone give a hand ?

I tried :

LOAD*,

If(Date = MonthEnd(Date), 1,0) as MonthEnd_Flag;

...but it does not seems to work properly

Thanks in advance

Guillaume

[Martijn_W]

Hi Guillaume,

Have you tried forcing the Date into the same format? 

If( Dayname(Date) = MonthEnd(Date), 1,0) as MonthEnd_Flag

The syntax is correct, I can't think of any other reason why it should not work.

Martijn

[guillaume_gorli]

Hi Martijin,

Does not seem to work :

I attached qvw if it can help to see where i am going wrong

[Martijn_W · Accepted solution]

I found the problem. The Dates are actually in a Datetime format behind the scenes. You can use this to remedy it:

If( floor(Date) = floor(MonthEnd(Date)), 1,0) as MonthEnd_Flag;

[guillaume_gorli]

Wonderful ! Thanks a lot Martijn !

[Martijn_W]

I found the solution by looking at the function result of Monthend. For reference, you can look at this article, in the table, you'll see that the function Monthend() returns a datetime instead of just a date. By using floor(), you eliminate the time portion by rounding down to a whole number. Dates and Datetime values are stored as a number, 1 for each day.

https://help.qlik.com/en-us/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/DateA...

[guillaume_gorli]

Very interesting Martijn, thanks again.