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):
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:
ISNULL(SUM([Value] WHERE [Type]="Forecast")) AND
IS(LOOKUP(SUM([VALUE] WHERE [TYPE]="Actual"),1))
Help? Thank you!
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.