4 Replies Latest reply: Sep 23, 2011 12:52 PM by Kiran Rokkam RSS

    Setting an expression as a variable.

      Hi all,

       

      I am working on an app that allows a user to select which expressions to be visible on the chart. ( I've attached the .qvw test)

      I've created an inline table which uses a variable for each expression, but it doesn't seem to be referencing the variables correctly-so it seems.

       

      I've defined each variable:

      set vAvg='AVG([Actual value])';

      set vGroup1='AVG({$<Institution={[ECSXIYXBHRU HLCDTNSMCJ (RVQNOMBWF EOJFKWLJXVL)],[XMVCOCEF WKH HSMEEZ RVENBEX]}>}[Actual value])';

       

      then in the inline table it uses these like so:

      Metric:

      replace LOAD recno() as MetricSequence,*

      INLINE [

      Metric, Expression

        Average, $(vAvg)

        Group1, $(vGroup1)

      ];

       

      Unfortunately it isn't really working in the radar chart.

      Any ideas on how I can change this, or help.

      I need to get this functionality working, as not everyone will have version 11(conditional expression show)

       

      Much appreciated

        • Setting an expression as a variable.

          I have this expression:

          sum(aggr(sum({$<Institution= p(Institution2) >} [Actual value]*[Number of Respondents]),[Question number]))

          /sum({$<Institution= p(Institution2) >} [Number of Respondents])

          which I'd like to put into a variable. ie. vPeerGroup.

           

          This is my script :

          SET vMyGroup='sum("Actual value")';

          SET vAvg='avg("Actual value")';

          SET vPeerGroup=

          sum(aggr(sum({$<Institution= p(Institution2) >} [Actual value]*[Number of Respondents]),[Question number]))

          /sum({$<Institution= p(Institution2) >} [Number of Respondents])';

           

          Metric:

          replace LOAD recno() as MetricSequence,*

          INLINE [

          Metric, Expression

            My Group, $(vMyGroup)

            Average, $(vAvg)

            Peer Group, $(vPeerGroup)

          ];

          Metrics:

          replace LOAD concat(Expression,',',MetricSequence) as AllExpressions

          RESIDENT Metric;

           

          LET vMetrics = 'pick(MetricSequence,' & peek('AllExpressions') & ')';

           

          DROP TABLE Metrics;

           

          But I need some help with the vPeerGroup variable, as I think it needs '' and to be changed a bit.

           

          Any ideas?

          • Setting an expression as a variable.

            There was a expression error in vMetrics. I modified it as following and radar chart is working.

            pick(MetricSequence,AVG([Actual value]))

             

            Kiran.

              • Setting an expression as a variable.

                Hi Kiran,

                 

                Thanks for the tip.

                But where do I put this?

                So, I must change LET vMetrics = 'pick(MetricSequence,' & peek('AllExpressions') & ')'; to

                LET vMetrics = 'pick(MetricSequence,AllExpressions)';??

                  • Setting an expression as a variable.

                    Your current output of vMetric is pick(MetricSequence,AVG([Actual value). Tweak your function to get the value as, pick(MetricSequence,AVG([Actual value])). I couldnt test as reload wont work as well as Metrics table is dropped.

                     

                    I recommend to not drop metrics table and dynamically calcuate vMetric. It will help you to include more dynamic expression selection.

                     

                    Hope this works out,

                    Kiran.