
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Add a flag in script to identify last day of the month
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Martijin,
Does not seem to work :
I attached qvw if it can help to see where i am going wrong

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Wonderful ! Thanks a lot Martijn !

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Very interesting Martijn, thanks again.
