topic Re: Reducing if else statements through calculation dimension in QlikView

Reducing if else statements through calculation dimension

abe786 — Wed, 29 Nov 2017 15:57:37 GMT

I'm trying to develop a method to save on if else statements by creating a dimension which will contain the calculation and it will be linked to dimension which is present in my chart. As an example , my dataset is below and my calculation dimension is below

Data:

LOAD * Inline

[ Product, Year, Sale

A, 2013, 10

A, 2014, 20

B, 2013, 40

B, 2014, 50

];

Calc:

LOAD * Inline

[Product, CalcDim

A, sum(Sale)

B, avg(Sale)

];

In my chart i have product and expression which will change according to the product through calcdim dimension.

I haven't been able to achieve it, as I think while evaluating the calcdim in expression, qlikview is not taking in account the dimension corresponding to the row in chart hence that is why it works if I select a single product but not when there are multiple.

Can anyone suggest some ways, I can accomplish the same , if possible ? Sample qvw is attached. !

Re: Reducing if else statements through calculation dimension

sunny_talwar — Wed, 29 Nov 2017 16:02:18 GMT

I don't think this is possible because dollar sign expansion only take places once per chart... and since CalcDim takes two values without selection, this approach will only work when only one possible value of CalcDim is there... otherwise you will have to use either if or Pick() function approach here

Re: Reducing if else statements through calculation dimension

sunny_talwar — Wed, 29 Nov 2017 16:03:58 GMT

This is not optimum, but I guess this is what you can do

Pick(Match(Product, 'A', 'B'), Sum(Sale), Avg(Sale))

Re: Reducing if else statements through calculation dimension

srik00001 — Wed, 29 Nov 2017 20:01:38 GMT

I tried with your sample using the replace function but as Sunny said, ran into $ expansion issues.

I have a similar requirement to create a Global Variable that can be used against every metric.It would be cool if anyone has any ideas or a different approach to the same.