Only() and Expressions as Variables

[#1]

Good morning delightful people

I have two QVWs that differ slightly; they do need to be two separate QVWs.

I was trying to so a little future proofing and thought I would centralise all expression in a sort of config. file. This would mean I would only have to update the expression once. I was wondering the best way to do it.

For example:

TABLENAME:

ChartID	Expression	Variable
CH01	=Sum(Sales)	vSales
CH02	=Count(Orders)	vOrders
CH02	=Count(DISTINCT SalesPerson)	vSPerson

One route was to use a loop and create a variable to create a variable for every row:

LET nRows = NoOfRows(TABLENAME);
FOR i = 0 to $(nRows)

LET vVariable = Peek(Variable,$(i));
LET $(vVariable) = Peek('Expression',$(i));
NEXT i

This works fine if I remove the leading "=", but it's very messy; creating 400+ variables.

I'd rather create a table in the data model and do something like this:

=Only({<Variable={'vSales'}>} Expression)

But this method doesn't calculate the value of the expression in a chart; in brings back the text string.

---

Anyone have an elegant way of doing this?

Thanks in advance.

[lironbaram · Accepted solution]

try to change to $(=Only({<Variable={'vSales'}>} Expression))

[Peter_Cammaert]

You cannot dynamically change an expression in this way, as optimal techniques like $-sign substitution and set analysis are evaluated before the chart is recalculated.

QlikView has an evaluate() function that evaluates the expression as presented by the single text-parameter. unfortunately this is a script-only function. It doesn't work in chart expressions.

Why can't you simply use this in your chart expression field (keep the '=' character in the original data)?

$(vSales)

We do this all the time when making use of expressions that can be externally configured (in an Excel or some other file)

Peter

[#4]

You know you've been a dummy when the question takes longer to write than the answer!

Thank you!

[Peter_Cammaert]

It certainly is less complex...

[#6]

Thanks for your response.

There are around 450 expressions in each of the two QVWs and creating that many variables was really messy/confusing.

For example, I created a composite key for each variable that looks like this:

vCH630_877203594_Exp_79

The other reason was that, it was calculating before the chart and ignoring the chart dimensions. Perhaps I'm missing something with that method.

[Peter_Cammaert]

You pointed out the tradeoff: either a lot of variables, or complex expression work.

The problem with your variables getting calculated before the chart won't occur if you correctly assign the proper strings to the variables in your script. $-sign substitution will happen outside the chart, but not the expression evaluation itself. Double equal signs are also a common mistake, as they block all expression evaluation.

Best,

Peter