Pull back Max(MonthYear) select that in not null

[#1]

Im trying to create a logic that does two things. Firstly, it should aggregate the values from the Max Month for a current selection. However, if the MaxMonth aggregation is Null/missing it should then pull the aggregation from the next previous maxMonth, and so on.(ie max(YearMonth)-1) and so on.

The reason I need this functionality is that some of the max month values might be missing in my data set, but still need to display the nearest most relevant Max month...

Month,Value

AUG, 8

SEPT, 10

OCT,  -

NOV, -

DEC, 5

Scenario 1 - Select sept and Oct - Value should 10

Scenario 2 - Select Sept, Aug - Value Should be 10

Scenario 3 - Select Nov, Oct, Sept and Aug Value should be 10

This is what I currently have but only works if I have dimensions selected and it only currently goes back one month.

if(len(avg({<MonthYear={"$(=MaxString(MonthYear))"}>}FIELD)/100) = 0, avg({<TempDate={'>=$(vMaxMonthLess1)'}>}FIELD/100),

avg({<MonthYear={"$(=MaxString(MonthYear))"}>}FIELD)/100)

vMaxMonthLess1 = Date(AddMonths(vMaxMonth))

vMaxMonth = date(max(REP_MONTH))

TempDate is just a date DD/MM/YYYY

Hope someone can help

[Anonymous]

Ryan,

You can find max month with data using:
max(if(len(Value), Month))

Hope you can figure out the rest

Regards,
Michael

[#3 · Accepted solution]

Perfect Michael!

So this is how I ended up with fix from your help

max(if(len(VALUE), date(MonthYear))) < This pulls back the latest month (Date) that contain values.

Steps

1. I created a new variable called vMaxMonthValue and assigned it this value: =max(if(len(VALUE), date(MonthYear)))

2. I then added the variable into the set Analysis statement

sum({<TempDate={'>=$(vMaxMonthValue)'}>)VALUE)

And presto. It works. Hopefully someone else can benefit from this

Thanks