Set Analysis Issue in Straight Table using Variable

[marc_wintjen]

Hi everyone,

I'm stuck trying to figure out why my Set Analysis expression is not dynamically calculating for each dimenional value when using a variable and dollar sign expansion.  My chart only has one dimension.  My expression is using aggrs with other dimensions not found in the chart but does calculate without issue when I use it in the expression.

max(aggr(rangemax( SUM(Sales)),Dimension1,Dimension2,Dimension3))

-
min(aggr(rangemax( SUM(Sales)),Dimension1,Dimension2,Dimension3))

When I assign it to a variable vSalesVariance, it works fine as well if I replace the Expression with this value "=$(vSalesVariance)"

So then I need to lookup the result of the the variable value against another field value so I'm using Set Analysis as follows in the 2nd Expression:

avg({$<[VarianceLookup]={'$(vSalesVariance)'}>} Score)

I've tried variations using single quotes, double quotes, etc.  The only time it calculates the correct value is if I actually select only 1 dimensional value.  Otherwise, it repeats the same calcuation for each row.

Anyone have some advise on why this is not working?  Thanks!

Marc

[shree909]

Hi what i found from your  qvw file.

avg({$<[VarianceLookup]={'$(vSalesVariance)'}>} Score) 

In this expression what ever the variable value ur providing that is not matching with the  variance lookup value.

so qv is gving the null values ,if you test the expression with values it will work..

When you goto the variable overview add an  = sign to the expression

[marc_wintjen]

Thanks for your quick response - I mocked up fictious data in this example so I expect to find some issues with null values but even if I add the value "10" to the Lookup Table, it still comes back with null in the yellow column for all rows.  I need it to return "40" for the row with the value of "C" and null or 0 for all other rows since they don't have a match to the Lookup.