topic Re: Calculated variable in expression with set analysis in QlikView

Calculated variable in expression with set analysis

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculated variable in expression with set analysis

sasikanth — Wed, 03 May 2017 10:34:45 GMT

HI,

Try like this

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

Re: Calculated variable in expression with set analysis

marcus_sommer — Wed, 03 May 2017 10:39:14 GMT

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

Re: Calculated variable in expression with set analysis

tresB — Wed, 03 May 2017 10:39:58 GMT

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

Re: Calculated variable in expression with set analysis

jessica_webb — Wed, 03 May 2017 10:44:28 GMT

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

Re: Calculated variable in expression with set analysis

jessica_webb — Wed, 03 May 2017 10:47:04 GMT

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

Re: Calculated variable in expression with set analysis

jessica_webb — Wed, 03 May 2017 10:51:18 GMT

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

Re: Calculated variable in expression with set analysis

tresB — Wed, 03 May 2017 10:56:05 GMT

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?

Re: Calculated variable in expression with set analysis

marcus_sommer — Wed, 03 May 2017 10:58:03 GMT

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

Re: Calculated variable in expression with set analysis

jessica_webb — Wed, 03 May 2017 11:00:08 GMT

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.

Re: Calculated variable in expression with set analysis

jessica_webb — Wed, 03 May 2017 11:04:27 GMT

That is exactly it!

Thank you so much Marcus, really appreciate your help with this!

Best wishes,

Jess

Re: Calculated variable in expression with set analysis

Thu, 04 May 2017 07:25:42 GMT

How would the code look if you want to use two dimensions in this set analysis?

Re: Calculated variable in expression with set analysis

jessica_webb — Thu, 04 May 2017 07:28:44 GMT

Hi Laura,

I added another dimension as follows:

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

With Dim2 being my second dimension and that worked just fine.

Re: Calculated variable in expression with set analysis

Thu, 04 May 2017 09:27:31 GMT

Thanks a lot for your fast respond, it works!