Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Set expression to a long expression with multiple components

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!

3 Replies
MVP
MVP

Re: Set expression to a long expression with multiple components

Try like:

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

Not applicable

Re: Set expression to a long expression with multiple components

Thank you! It worked with a bit adjustment:

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

MVP & Luminary
MVP & Luminary

Re: Set expression to a long expression with multiple components

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