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.
My only concern is that if conditions in the dimensions usually slow down the application, so if there is any possibility of doing this in the script, I would recommend you going that route.
CallTime and CreationDate are loaded from seperate tables. Should I load this calculation in the script by joing these two tables?
I do understand that a variable can be referenced in the dimention, but can it be set there? It doesn't seem that I can set it there although it makes logical sense for me to do so. So if I have to define it in the load script it seems that I can't reference the previously loaded values but rather have to do the calculation in a query that joins the tables together. Is this right? @sunindia
Join is NOT the only option available when you do this in the script. There are other like using mapping load together with apply map or Lookup function to get it done as well.
