1 Reply Latest reply on Aug 30, 2013 12:04 PM by Jim Wahl

    Max Date When IF Statment is True

    jason ricapito

      I have the below IF calcuation that acts like a flag that I can color a table of data with.  My table of data is simply the attrributes in the IF statement paritiioned by months.  The calcuation resolve to true for mutiple months.  However, I want only to highlight the partiion (across) with the maxium date where this If statemetn resolve to true.  So, in the below table I only want to highlight the Apr partition.  Thanks for the help.

       

      MonthOUT % of TotalAPP % of TotalAWD % of Total
      Jan5%9%9%
      Feb25%20%5%
      Mar25%25%20%
      Apr5%5%5%

       

      IF

      [OUT % of Total] < .1

      AND

      [APP % of Total] < .1

      AND

      [AWA % of Total] < .1

      THEN

      'Recovery Rate End Month'

      ELSE

      ''

      END

        • 1. Re: Max Date When IF Statment is True
          Jim Wahl

          Hi Jason,

           

          I'd probably break this into two parts.

           

          First, a formula to determine if the month exceeds the thresholds and if so returns the DATE.

          Months Meeting Threshold =

          IF [OUT % of Total] < .1
              AND [APP % of Total] < .1
              AND [AWD % of Total] < .1
          THEN [Month]
          END
          

           

          Second, a table calc to find the LAST() value in the above formula and compares that to the month on the row.

          Recovery Rate End Month =

          MIN([Month]) == LOOKUP(MIN([Months Meeting Threshold]), LAST())
          

           

          Now you can drop Recovery Rate End Month on the color shelf and it will evaluate to TRUE only for the last month.

          2013-08-30 22-02-41.png

           

          Jim