Setting an expression as a variable.

[#1]

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

[#2]

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?

[#3]

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

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

Kiran.

[#4]

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)';??

[#5]

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.