I calculate a value based upon two other record values. Here is an example:
round(60*24*(10000*Num(CallTime) - left(subfield(date#(CreationDate),'.',2),4))/10000)
The issue is that I want to add 720 if it is less than -270 and then set to null if it is still less than 0 or more than 270.
I can do this with a bunch of nested if statements in the calculated dimension but it will look cumbersome and be error prone if I need to make changes. Is there a place where I can calculate the value for each record and then refer to it by the variable name? I tried to do it in the load but didn't have much luck. I thought it would be more logical to define it in the dimension but it didn't seem possible. Not sure which approach is best.
On a somewhat unrelated note I also was surprised that Median was not an option when totaling values for expressions. Of course I wanted to get the Median for the calculated dimension which doesn't seem doable.
