Nested Variables in Aggr

[devan9876]

I have a chart with one calculated dimension and one expression.The vTest variable is used as a parameter for the vRank Variable and an integer is passed in as a parameter to the vTest variable.  

This is what I have for my formulas and it returns  "//Error in calculated dimension".

Chart:

Dimension:Aggr($(vRank($(vTest(12)))),GROUP)

Expression: count(distinct GROUP)

Variable Definitions:

vTest: Sum({<TYPE={$1},INDEX={1}>}QTY)/Sum({<TYPE={$1},INDEX={1,2}>}QTY)

vRank: if($1>=.9 and $1<=1,1,
if($1>=.7 and $1<=.8999,2,
3))

[Gysbert_Wassenaar]

Try Aggr($(vRank($($(vTest(12))))),GROUP)

[devan9876]

It doesn't return an error, but it still isn't evaluating correctly.

It only returned one row in the straight table.

[Gysbert_Wassenaar]

Please post a small qlikview document with example that demonstrates the problem.

[devan9876]

I can't attach the actual document, but I created a sample file that replicates the problem.

[marcus_sommer · Accepted solution]

Your approach couldn't work in this way because if you with vTest just transferred the expression as string the included commas would be interpreted as parameters from vRank. If you evaluate vTest with a $-sign expansion it would be creating an adhoc-variable and evaluated globally at the first step and applied to all rows - means within the aggr there would be only a single value for each GROUP.

You may change the set analysis syntax within vTest to create an expression which don't have included commas, for example by using something like this (as expression-string):

SUM({<TYPE={$1}> * <INDEX={1}>}QTY)/SUM({<TYPE={$1}> * <INDEX={"(1|2)"}>}QTY)

but even if it's possible to use nested variables it's seldom worth to play with such complex (syntax) constructs.

- Marcus

[devan9876]

That works, Thanks Marcus.

Are there any performance implications changing the set analysis to what you posted compared to what I had initially?

And if its not good to set it up like I have what would you suggest instead?

The actual app I is more complex than the example posted. There are around 40 different metrics and each one has a 5 tier rating scale with 1 being perfect and 5 being awful. The scale is usually different for each metric.

I thought it would be easier to have one variable for each metric that calculates the actual score and one that accepts the score as a parameter and asigns the 1-5 score so if I need to change the calculation or ranking for any given metric I only have to change it in one variable.

[marcus_sommer]

The change from {1, 2} to {"(1|2)"} makes no differences because it's really just another notation but I don't know if changing from < F1 = {x}, F2 = {x} > to < F1 = {x}> * < F2 = {x} > has an impact on the performance. I never tested it but I assume there shouldn't much difference between them.

I think ou need to test it with your data especially because you executes it within aggr() which could require a lot of performance (particularly if the fields within the expression and the grouping fields come from different tables).

In regard to the mentioned performance it could be an alternatively to use a pick(match()) instead of nested if-loops, like:

pick(match(floor(Expression, 0.1), 0, 0.1, 0.2, ..., 0.9, 1, ...), 'a', 'a', 'a', ..., 'c', 'c', ...)

- Marcus

[devan9876]

I am curious, are there any other work arounds to getting the nested variables to work aside from changing the set analysis to not have commas?

Also, would the pick match floor solution you mentioned still work if the size of the bands are not equal?

For example rank 1 might be .995-1 and rank 2 might be .8-.9949.

[marcus_sommer]

There is no way to mask the commas. Therefore you will for nesting variables always need any workaround like my suggested syntax-changes and sometimes it's also possible/necessary to implement some replace() logic - using any dummy-chars and replacing them within a certain process-step - and/or splitting the variable-content into multiple parts/expressions and parameters. I don't think that the last mentioned steps may be applicable in your case - it was just to mention further possible approaches and to highlight that such constructs could be really complex and the efforts to develop and to maintain them could be enormous.

The mentioned match(floor()) worked only if the rounding could be appropriate defined respectively matched with the provided value-list. Quite often it's just necessary to make the value-list enough atomic even if it mean to provide a quite long list of values (this mustn't mandatory be a manual listing else it could be just created within an excel-table with expressions and/or the usual copy & paste - it's often a rather quick work) and those listing could then be loaded within a table and applied within the match with something:

$(=concat(MatchListValues, ',', MatchListRecno))

Beside this it might be also possible to switch within the floor() between various roundings and/or to combine the match-expression respectively the pick/match-result with an alt() or rangemin/max() to fetch exceptions and lower/upper-restrictions to reduce the amount of such valu-listing.

- Marcus