2 Replies Latest reply: May 8, 2012 10:29 PM by Matthew Green RSS

    Return the max month

    Matthew Green

      Hey all,

       

      I bring in a set of data that contains date placeholders for future data, i.e. For the current year the records all exist, but the values are  0

      Month
      Value

      Jan

      12
      Feb41
      Mar8
      Apr24
      May2
      Jun

      0

      Jul0
      Aug0
      Sep0
      Oct0
      Nov0
      Dec0


      and there are a number of years in the future.

       

      I have no issue capturing the month when it has been selected, however when none is selected, I am struggling to have a text box display Mar, since Mar is the current month.  I realise I can just use MONTH(Today()), but I also then need it to display the max month when i select a range of months.

       

      I also need to capture the value of an expression for the current max month..

       

      I've attached a reduced version of the report.  Whart I want the two text boxes to show is the Month, which is Mar, and the value for the 2011-12 value of Mar, which is 82.25%.  If you highlight Jan-Feb, then it should Show Feb, and the value for Feb. 

        • Re: Return the max month
          Sokkorn Cheav

          Hi mgreen85,

           

          Let try this expression for your text objects:

          1. Show max month :

               =PICK(MAX(DateMonth),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

          2. Show value for max month :

               =NUM(Avg({$<data_measure={'Actual'}, KPIDate = P(DateValue), month = {$(=PICK(MAX(DateMonth),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))} >}data_value)/100,'###.##%')

           

          See the sample attached file.

           

          Let me know if this one help you.

           

          Regards,

          Sokkorn

            • Return the max month
              Matthew Green

              That works partly.

               

              If I don't have a month selected though, and the current year is only populated to May like the above table, how do I make it identify May as the max month instead of December.

               

              Also, since i'm working in the financial year, I need to switch December for June, how would I go about this?