Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Ryan,
You can find max month with data using:
max(if(len(Value), Month))
Hope you can figure out the rest
Regards,
Michael
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