Dynamic Variable calculation

[#1]

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.

[Jason_Michaelides]

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

[#3]

attached

[IAMDV · Accepted solution]

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,

DV

[#5]

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))

[IAMDV]

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,

DV