Set analysis max date -1

[david_pearson]

Hi all

have have a dataset with a date field (dd/mm/yyyy format). This, depending on the load date of the data, will show the last date in that quarter. i.e. loaded in any date in Q1 2021 will be 31/03/2021, Q4 2020 is 31/12/2020 etc. This field is called LOAD_DATE and is part of my autocalendar.

I have set analysis to calculate based on the max LOAD_DATE field which appears to work. I want to do the same but for the next available month before the max load date. In this case the max is 31/03/2021, I want the set analysis to go against 31/12/2020. Obviously I want this to change as new data loads in.

Current formula for max date is below. i had thought i could just add -1 but this goes back 1 day. 

if(count(distinct {<[LOAD_DATE.autoCalendar.Date] = {"$(=Max([LOAD_DATE.autoCalendar.Date]))"}>} PH_REF)=0,0,
sum(aggr(count(distinct {<[LOAD_DATE.autoCalendar.Date] = {"$(=Max([LOAD_DATE.autoCalendar.Date]))"}>}COVER_CODE),PH_REF))/
sum(aggr(count(distinct{<[LOAD_DATE.autoCalendar.Date] = {"$(=Max([LOAD_DATE.autoCalendar.Date]))"}>} PH_REF),PH_REF)))

thanks 

[brunobertels · Accepted solution]

Hi 

To get the previous month : 

addmonth(Max([LOAD_DATE.autoCalendar.Date]),-1) : 

31/03/2021 will become 28/02/2021 

in your case as the previous month seems to be the las month of previous quarter use 

quarterend() function : 

quarterend(Max([LOAD_DATE.autoCalendar.Date]),-1) will give you 31/12/2020 

regards