2 Replies Latest reply: May 8, 2013 12:28 PM by Marc Wintjen RSS

    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

       

       

        • Re: Set Analysis Issue in Straight Table using Variable
          sree anaarasi

          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

              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.

               

              Set Analysis Issue.JPG