topic Re: Re: Re: Dynamic Variable calculation in QlikView

Dynamic Variable calculation

Wed, 11 Jun 2014 16:20:55 GMT

Would it be possible to assign a variable as a field for every row and then have it change the metric calculation dynamically based on user input as shown below?

var1 = 1

var2 = 2

Table:

Col1, Col2, Col3, measure

rec1, dim1, var1, 1

rec2, dim1, var1, 2

rec3, dim2, var2, 3

This way the user can do a variable input to their heart's content for the what-if simulation.

The INPUTFIELD concept is limiting in that it is an all or nothing feature and to selectively apply it to dim1 and dim2 with var1 and var2 respectively is not working out. Looking for creative ways to handle the what-if situation.

I also tried doing SUM(Measure)*$(=Col3) and $($(=Col3)) to no success.

Re: Dynamic Variable calculation

Jason_Michaelides — Wed, 11 Jun 2014 16:31:49 GMT

I'm not quite following what you want...can you provide examples?

Re: Re: Dynamic Variable calculation

Wed, 11 Jun 2014 17:49:34 GMT

attached

Re: Re: Re: Dynamic Variable calculation

IAMDV — Thu, 12 Jun 2014 09:32:46 GMT

Hi Amir,

You can get this working in 3 steps.

Step 1: Set Variable

vTest =Concat(DISTINCT Chr(39) & F1 & Chr(39), ', ')

vMyValue =Concat( Chr(39) & Chr(36) & '(' & F3 & ')' & Chr(39) , ' , ')

Note: You need the "=" sign before the Concat() function while assigning the expressions in the above variables.

Step 2: Assign Dimension

Add [F1] or Primary Key in your table as Dimension Field

Step 3: Use the below Expression in the Expression Tab

Pick( Match(F1, $(vTest)), $(vMyValue)) * [F4]

Now, if you change the value in the variable you see the changes in the straight Table. See the attached and hope this is what you need.

Cheers,

Re: Re: Re: Dynamic Variable calculation

Thu, 12 Jun 2014 13:30:16 GMT

DV,

This is a great suggestion. It worked well. However, in my case, this should work without the primary key in the table.

I am thinking an Aggr() possibly. That does the trick.

SUM(Aggr(Pick( Match(F1, $(vTest)), $(vMyValue)) * SUM([F4]), F1))

Re: Re: Re: Re: Dynamic Variable calculation

IAMDV — Thu, 12 Jun 2014 14:16:19 GMT

I'm glad it worked. Makes sense to use Aggr() if you don't have primary key or probably add all other columns to make it unique.

Cheers,