Calculated variable in expression with set analysis

[jessica_webb]

Hi,

I have a variable in my script:

Let vValue = 'Avg((Points-Target)/Difference)';

What I would like to do, is use this in an expression with set analysis. For example:

=ONLY({<Type={'A'}>} $(vValue))

But this just gives me a '-' null value.

If I just put =$(vValue) and then select 'A' from a filter for Type, it works fine. So why can't I do this within my expression?

I need to keep my 'A' dimension within the expression, as I plan on using several expressions within the chart, with the appropriate dimension coming from a different field each time.

Any help appreciated.

Thanks,
Jess

[sasikanth]

HI,

Try like this

Avg({<Type={'A'}>} ((Points-Target)/Difference))

[marcus_sommer]

With this approach you nest two aggregations without an aggr-function which is mandatory for it. You may change to something like this:

=ONLY({<Type={'A'}>} aggr($(vValue), Dim1, Dim2))

whereby Dim1 and Dim2 are placeholder for the dimensions which are related to this calculation - normally those dimensions which are used within your chart.

- Marcus

[tresesco]

Try with Parameterised Variable. May be like:

vValue = 'Avg(' & $1 & '(Points-Target)/Difference))'

Then call the variable like:

=$(vValue({<Type={'A'}>} ))

Edit: corrected a brace

[jessica_webb]

Thanks Sasi, but I need to use the variable rather than the calculation.

[jessica_webb]

Hi Marcus,

I did read an article on the aggr function, but wasn't sure it applied to my situation. Quite possibly I'm wrong about that!

I'm slightly confused on the Dim1, Dim2 part - Type='A' is the dimension I want to use. So do I just repeat this in place of Dim 1, Dim2?

Thanks,
Jess

[jessica_webb]

Hi Tresesco,

Well the expression is accepted, but it gives me the same value as if I didn't have the {<Type={'A'}>} part in there.

It just evaluates '=$(vValue)'

Thanks,
Jess

[tresesco]

Yes. If you don't include the set part, it would evaluate the average in the context. What do you expect the output to be without the set part passed?

[marcus_sommer · Accepted solution]

You could try this:

=ONLY({<Type={'A'}>} aggr($(vValue), Type))

but if it didn't work we will need some more context where it should be used and which data should return which result.

- Marcus

[jessica_webb]

With just =$(vValue) I get a figure of 10 - this is the value for 72 participants.

What I want, is the 'vValue' (which should be 5) for only the participants who are included under Type 'A' - this is just 39 of the participants.

If I put 'Type' as the dimension, and 'vValue' as the expression, I get '5' next to Type 'A'.

So this is the result I want, but I cannot use the regular dimension field - instead I need to include my dimension in the expression.