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...



      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),



      vMaxMonthLess1 = Date(AddMonths(vMaxMonth))

      vMaxMonth = date(max(REP_MONTH))



      TempDate is just a date DD/MM/YYYY



      Hope someone can help