1 Reply Latest reply on Aug 19, 2016 5:57 PM by Andrew Watson

    using lookup inside formulas

    Sarah McGraw

      Hello,

       

      I've gotten excellent help here so far, so here's another question.

       

      I've seen many example formulas more or less like this, where fields are called inside formulas. (This particular formula is probably not what I'm looking for, but it will do as an example):

      Capture.PNG

      Unfortunately, my data is structured in such a way that this will not work. I don't have fields called "Forecast" and "Actual" that I can call in a formula. Instead, I have a field called [Value] with all numbers in it, and a field called [Type], with "Forecast" and "Actual" being two [Type] values.

       

      What I need is a way to call a value from [Value] based on [Type]="Forecast", or [Type]="Actual", something like this nonsense pseudocode:

       

      IF

           ISNULL(SUM([Value] WHERE [Type]="Forecast")) AND

           IS(LOOKUP(SUM([VALUE] WHERE [TYPE]="Actual"),1))

      etc.

       

      Help? Thank you!

        • 1. Re: using lookup inside formulas
          Andrew Watson

          It's not too clear what you're trying to do in your pseudocode :-)

           

          The formula to get Actual would be: IF [Type] = 'Actual' THEN [Value] END

           

          The formula to get Forecast would be: IF [Type] = 'Forecast' THEN [Value] END

           

          You would sum those measures when using them. Does that push you in the right direction?

           

          Note LOOKUP was used in the previous example to return the previous value.