topic Re: Set expression to a long expression with multiple components in QlikView

Set expression to a long expression with multiple components

Wed, 25 Nov 2020 16:16:04 GMT

I have a model with four variables A, B, C, 😧

A = B * C / D

where

B, C, D are all variables with long expression:

B = sum(...)

C = count(...)

D = avg(...)

What I want is to apply a filter to calculate me A value of retail year 2016.

Below are two approaches I did:

Approach 1:

B_2016 = sum({$<retailyear={2016}> ...)

C_2016 = count({$<retailyear={2016}> ...)

D_2016 = avg({$<retailyear={2016}> ...)

A_2016 = $(B_2016) * $(C_2016) / $(D_2016)

Approach 2:

A_2016 = sum({$<retailyear={2016}> ...) * count({$<retailyear={2016}> ...) / avg({$<retailyear={2016}> ...)

Both approaches worked, but nasty: in Approach 1 I created too many variables, and in Approach 2 I created a extremely long and unreadable expression.

Is there a neat way, I can do something like:

A_2016 = <retailyear={2016}> B * C / D

Thank you!

Re: Set expression to a long expression with multiple components

tresB — Fri, 16 Jun 2017 08:34:35 GMT

Try like:

A_2016 = Only({<retailyear={2016}>} Aggr(B * C / D, retailyear))

Re: Set expression to a long expression with multiple components

Fri, 16 Jun 2017 09:13:03 GMT

Thank you! It worked with a bit adjustment:

A_2016 = Only({<retailyear={2016}>} Aggr($(B) * $(C) / $(D), retailyear))

Re: Set expression to a long expression with multiple components

marcus_sommer — Fri, 16 Jun 2017 11:14:43 GMT

One way could be to use variables with parameters, maybe in this way:

B = sum({$<retailyear={$1}>...)

C = count({$<retailyear={$1}>...)

D = avg({$<retailyear={$1}>...)

and then:

A = $(B(2016)) * $(C(2016)) / $(D(2016))

- Marcus