topic Setting an expression as a variable. in QlikView

Setting an expression as a variable.

Thu, 22 Sep 2011 13:29:51 GMT

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.

Thu, 22 Sep 2011 22:40:34 GMT

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.

Fri, 23 Sep 2011 00:50:08 GMT

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.

Fri, 23 Sep 2011 02:22:30 GMT

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.

Fri, 23 Sep 2011 16:52:01 GMT

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.