MAX Date of the last month end, whe weekly data is shown

[Silvia]

I am trying to pull in the month end for set analysis and in a table; but my data has a rolling 2 week and month end.  The data is a rolling 12 month and most recent 2 weeks.  I need to have my calculations looking at only the month end data to trend month over month changes, but if I use MAX for the date it will pull in the weekly data.  Is there any way to create a variable to tell he formula to only look at month end data, and not use the weekly?

Example of the data:

LOAD Date

1/11/2019

1/4/2019

12/31/2018

11/30/2018

10/31/2018

[pradosh_thakur]

compare the date with monthend(date) if same then consider else not.

[Silvia]

Not sure I understand.  How would I write that as a variable or in the formula?  I would need the most recent Month End date to appear.  In my example, at 1/11/2019 I would need 12/31/2018 to be the date to refer back to.  Same for 1/4/2019, I would need the formula to refer to 12/31/2018. 

[pradosh_thakur · Accepted solution]

ohk

Create a field in the script

load date,if(date=monthend(date),1,0) as monthend_flag

from table;

use max({<monthend_flag=1>}date)

[Silvia]

Thank you so much!