1 Reply Latest reply on Jun 25, 2013 7:41 AM by Mark Holtz

    How can I use a formula to determine which measure to use?

    Chad Kovac

      I'd like to use a parameter in a calculation but I want that parameter to use different parameters based on the age of a unit.


      Something like this:

      ('[Cost Year '+str(year(today())-[UnitYear])+']')


      So if I have parameters named Cost Year 1 through Cost Year 10 and the Unit Year is something like 2011 that would mean I want the value of Cost Year 2 because 2013-2011 = 2.


      I can't determine if there is any way to do this type of conditional parameter lookup.

        • 1. Re: How can I use a formula to determine which measure to use?
          Mark Holtz



          I'm guessing without seeing what your underlying data looks like, but I assume you are saying you want to "swap measures" based on the comparison of each record's [Unit Year] compared to today...


          So instead of creating a "lookup" as you put it, I think you just have to build a calculated field that uses an if statement against the [Unit Year] to determine which field to use as the measure field.


          For example, I am assuming you have 10 different columns that show CostYear1, CostYear2, CostYear3--each being a measure.


          Create your [MeasureToUse] field as:

          IF YEAR(TODAY())-[Unit Year] = 1 THEN [CostYear1]
          ELSEIF YEAR(TODAY())-[Unit Year] = 2 THEN [CostYear2]
          ELSEIF YEAR(TODAY())-[Unit Year] = 3 THEN [CostYear3] ...


          Does that help? If not, could you post a packaged workbook with your example?