18 Replies Latest reply: Oct 19, 2017 10:48 AM by Erica Trotter

# Last day of the Month Set analysis

Hello,

I am trying to write a formula that will show me the last day of the month data, here is what I currently have:

{<Date ={"\$(MonthStart(Min(Date))-1)"}>}

Please let me know what I should be doing differently for it to recognize it

Thanks!

• ###### Re: Last day of the Month Set analysis

Try this

{<Date = {"\$(=MonthStart(Min(Date))-1)"}>}

or this

{<Date = {"\$(=Date(MonthStart(Min(Date))-1))"}>}

• ###### Re: Last day of the Month Set analysis

Neither of those are working

• ###### Re: Last day of the Month Set analysis

What is your date field format?

• ###### Re: Last day of the Month Set analysis

M/DD/YYYY ex: 9/20/2017

• ###### Re: Last day of the Month Set analysis

Sum({<Date = {"\$(=Date(MonthStart(Min(Date))-1, 'M/D/YYYY'))"}>} Measure)

• ###### Re: Last day of the Month Set analysis

Avg({<Date = {"\$(=Date(MonthStart(Min(Date))-1, 'M/D/YYYY'))"}>} Goal)

its still not working, im using average, could this effect output?

• ###### Re: Last day of the Month Set analysis

It should not... can you share an image of the table where you add this as measure and hover over the expression header to show what the expression looks like? Make sure to now use any expression labels

• ###### Re: Last day of the Month Set analysis

the expression is: Avg({<Date = {"\$(=Date(MonthStart(Min(Date))-1, 'MM/D/YYYY'))"}>} Goal)

• ###### Re: Last day of the Month Set analysis

Your min date seems to be 9/1/2017 and you want to look at the data for 8/31/2017? But if the min date is 9/1/2017, there is no data for 8/31/2017

• ###### Re: Last day of the Month Set analysis

Hi sunny

As she is using DATE as a dimension and same is used in set analysis, will it not impact and show the result as blank ?

• ###### Re: Last day of the Month Set analysis

I don't really know the expected output here... but what I can see is that going back one day from the minimum date is not going to work

• ###### Re: Last day of the Month Set analysis

The data goes back to July, so there are multiple months, I am trying to look at the goal on the last day of the month. Is there another formula we could try?

• ###### Re: Last day of the Month Set analysis

So what value do you expect to see for 9/1/2017 and also 9/2/2017? Each of them should show 8/31/2017 value? Also, do you only look at this chart one month at a time?

• ###### Re: Last day of the Month Set analysis

Its going to be a line graph but only one point for each month on the last day

• ###### Re: Last day of the Month Set analysis

Oh so you are looking to get month end value for each month on a line graph? Why not create a flag for this in the script

LOAD Date = Floor(MonthEnd(Date)) as Flag

and then this

Avg({<Flag = {'-1'}>}Goal)

• ###### Re: Last day of the Month Set analysis

Whoohoo it worked, thank you so much!

• ###### Re: Last day of the Month Set analysis

hi

do you have month or date or year any date related field as dimension ?

• ###### Re: Last day of the Month Set analysis

Yes, I have a Year, Month Year,  Month, and Week dimensions