Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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