topic Re: Can I set a variable in Chart Dimension? in QlikView

Can I set a variable in Chart Dimension?

Fri, 17 Apr 2015 19:49:24 GMT

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.

Re: Can I set a variable in Chart Dimension?

petter — Fri, 17 Apr 2015 20:05:46 GMT

When you say "two other record values" - do you mean two different columns in a chart table?

Is CallTime and CreationTime in the same in-memory table?

Re: Can I set a variable in Chart Dimension?

sunny_talwar — Fri, 17 Apr 2015 20:07:45 GMT

You can def. use variables in your dimension. You can have one variable which does all the things, or you can have it in pieces.

(I mean this when I say pieces)

If($(vVar1) < -270, If($(vVar1) + 720 < -270, Null(), $(vVar1) + 720), $(vVar1))

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.

HTH

Best,

Sunny

Re: Can I set a variable in Chart Dimension?

Tue, 21 Apr 2015 18:41:25 GMT

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

Re: Can I set a variable in Chart Dimension?

sunny_talwar — Tue, 21 Apr 2015 18:45:29 GMT

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.

Best,

Sunny