Referencing multiple variables in a single expression

[#1]

I have multiple variables that store user input for the desired revenue for a product segment.  I am trying to display this information in a chart next to the relevant segment and the actual revenue.  I have named the variables using the pattern 'v' & product_data.segment & 'Rev'.  For example, I have vAthleticRev, vBootRev, etc.  I am trying to create an expression in a table that displays the values in these variables using dollar sign expansion.  The closest I get is creating a table with segment as the dimension and the following as the expression:

=$(=$(vDynSegment))

vDynSegment contains 'v' & product_data.segment & 'Rev'

This solution only works when I select the row, otherwise it comes out as -.  This tells me that it is calculating the segment before the table dimension is applied.  I have tried many combinations of = and $, and just pasting the concatenation string into the expression.

In short, I need to build the variable from a concatenated string using the dimension, and then get the value of the variable and display it in the expression column. 

If the user put 60000 into the vBootRev variable, and the actual revenue was 58000, I would like the row in the table to look like this:

Segment     Planned Revenue     Actual Revenue

Boot           $60,000                   $58,000

Thanks for the help.        

[swuehl · Accepted solution]

You are right that the dollar sign expansions are evaluated in TOTAL context, not in the context of your dimensions.

Try a pick/match combination

=Pick(Match(product_data.segment,'Athletic','Boot'),vAthleticRev, vBootRev)

[#3]

Thanks for the help.  That worked like a charm.  Any idea why the same formula is not working in the visual cues tab for another row in the table?  I want the actual revenue to turn green if it is above this planned number, but the >= formula box does not seem to take advantage of the pick/match formula the same way the expression formula did.  Does this mean the visual cues formulas do not reference the dimension?

[#4]

If you have only one dimension in your table, then add another calculated dimension which will show the value that user entered in the corresponding variable using similar syntax.

=Pick(Match(Product,'Casuality','Financial Lines','Speciality'),$(vCasuality),$(vFinancialLines),$(vSpecialty))

Then conditionally hide or show that calculated dimension using below expression to avoid showing '-' if user don't enter any value to the variable.

=If((Len(vCasuality)= 0  or Len(vFinancialLines) =0 or Len(vSpecialty)=0),0,1)

I hope I answered your question correctly. In case I understood your question wrongly , then please let me know.

[swuehl]

Tate,

I believe you are right, the visual cues expressions are only evaluated in total context, not per dimension line.

But you can use background / text color attribute expressions, go to expression tab and open the attribute expressions by clicking on the small plus sign next to the expression label on the upper left.

Select background color and enter a color expression, like

=If( Sum(FIELD) > Pick(Match(product_data.segment,'Athletic','Boot'),vAthleticRev, vBootRev), LightRed() )

Instead of Sum(FIELD), use the measure you want to compare, i.e. the measure expression itself.

See also attached.