Loading variables to a straight table based on dimension and expression fields

[#1]

Hi, wondering if anyone has some smart ideas about how to solve this

Is it possible to define a variable based on its position in a table, then return the value for that variable?

See the example below:

Dim	1	2	3
Sales	$(varSales1)	$(varSales2)	$(varSales3)
Volume	$(varVolume1)	$(varVolume2)	$(varVolume3)
Margin	$(varMargin1)	$(varMargin2)	$(varMargin3)
...	...	...	...

Each of the variables contain Set Analysis, and I can get each one individually to display in a text box, then build a "table" structure by moving the boxes around.

But it would be cleaner if this was one straight table, easier to export, etc.

What I would like to do is set an expression that essentially "concatenates" the column and row names into a variable name?

E.g. Column = (calculation) "1", Dimesnion = "Sales", Variable = $(varSales1)

Then the result displayed is the result calculation of the variable e.g. "=$(varSales1)"

Any ideas if this is possible?

Alternatively I could create this as a "Flat Table" in the load script and make the calculations there, but doing it this way would be much cooler!

[its_anandrjs]

Use a straight table for this

Eg:-

In dimension write

'Sales' and in expression followed by variables like $(varSales1) ,$(varSales2) ,$(varSales3)

same for

'Volume' and in expression $(varVolume1),$(varVolume2),$(varVolume3)

same for

'Margin' and expression $(varMargin1),$(varMargin2),$(varMargin3)

means create three straight tables

New Update:- And remove its caption and title from the chart and arrange tables in sequence Sales,Volume,Margin

[jonathandienst · Accepted solution]

Hi

You can do this. I am assuming that 1, 2, 3 along the top are 3 expressions. Define expression 1 like:

=Pick(Match(Dim, 'Sales', 'Volume', 'Margin'), $(varSales1), $(varVolume1), $(varMargin1))

This will apply the correct expression. The variables must contain legal QV code, without a preceding = sign, so that they will be expanded into the Pick(Match()) expression.

Do the same thing with expressions 2 and 3, just change the index number in the variable names.

HTH

Jonathan

[jonathandienst]

To add to that post: rather than Pick(Match(....)), you can use Pick(RowNo(), ...).

If you have many values of Dim, so the pick match (or Pick(RowNo()..) expression gets very long and awkward to manually capture and maintain, you can use the load script to construct the Pick() expression. I have done that with about 50 sub-expressions (read from a spreadsheet).

But be warned that this can be a performance killer - in my experience there is quite a calculation overhead with large Pick(Match()) expressions, so you would need to test  the performance of your application. This should not be a problem with less than 10 expressions, although that depends on the complexity of the expressions in the variables.

Jonathan

[its_anandrjs]

You can use any inline table also like

Dimension:

Load * inline

[

Dim_Name

Sales

Volume

Margin

];

and in chart use Dimenion Dim_Name and in expression variable followed by Dimension name.

[telepuzik]

Pase fnd the attached on how to acheive your rquest.

[#7]

Thanks Jonathan, Works like a dream.

Eventually the list will get quite long which might make the code unmanageable, but looks good so far!

[#8]

Thanks.

This does what I wanted to do.

I think the "Pick" function suggested by Jonathan is cleaner than nested 'if' statements and probably better for performance as well though