2 Replies Latest reply: Dec 4, 2012 12:46 PM by Ryan Hamill RSS

    Pull back Max(MonthYear) select that in not null

      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