topic Re: Set Analysis Issue in Straight Table using Variable in QlikView

Set Analysis Issue in Straight Table using Variable

marc_wintjen — Wed, 08 May 2013 16:12:10 GMT

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

Re: Set Analysis Issue in Straight Table using Variable

shree909 — Wed, 08 May 2013 16:20:25 GMT

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

Re: Set Analysis Issue in Straight Table using Variable

marc_wintjen — Wed, 08 May 2013 16:28:37 GMT

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.