Best Way to Setup This Code

[#1]

Hi,

I have some code that I'm using in one of my expressions in a straight table, and it works.  In one of the dimensions I am going to have to compare the value returned by the code from the expression below, to the quartile threshholds in another table to determine which quartile the value falls into.  There are 4 quartiles, so I am going to need to reference the value 4 times in the conditional statement in my dimension. I could paste this code into the conditional statement each time I am referencing the value, but that is going to make for one huge conditional statement, so I'd like to to find a cleaner way to do this.

As far as I can tell, it will not allow me to call the expression in the dimension. I tried pasting this code into a variable and calling it from my straight table , but ever single row of the straight table ended up with the same value in the field (which was the aggregated total for all of the records combined).  It did not consider only the individual profit center's values. Is there a way to get the variable to treat each row of the straight table separately?  Or is there another way besides a variable that would work better?

Thanks!

[Gysbert_Wassenaar · Accepted solution]

Using a variable should work as long as you don't put an = at the beginning of the expression in the variable. That will defer evaluation of the expression in the variable to where/when the variable is expanded in the expressions it's used in.

[#3]

Thanks for the reply, Gysbert. I tried simply removing the equal sign in the variable, but it didn't work.  It ended up showing the if statement in the field instead of the result.   I think I must not be understanding correctly.

Is there any way you can provide an example of this?

[#4]

Use dollar sign expansion to evaluate the contents of the variable.

For example, if your variable is vFormula then use $(vFormula) as your expression. Note that if you want the expression to calculate over the dimension then do not use an = sign in the definition of the variable. 

[#5]

Thank you to both of you.  It works!