topic Re: Nested expression in QlikView

Nested expression

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

I am trying to write expression Avg(sum(x)) while creating a measure but am unable to do so.

Re: Nested expression

sunny_talwar — Fri, 14 Jul 2017 11:53:13 GMT

You will need to add Aggr() in between them

Avg(Aggr(Sum(x), ListOfDimensions))

Re: Nested expression

siddharthsoam — Fri, 14 Jul 2017 12:09:55 GMT

Can you please elaborate listofdimensions by a working example

Re: Nested expression

sunny_talwar — Fri, 14 Jul 2017 12:15:08 GMT

Here is an example

Country, State, Population

US, NY, 10

US, NJ, 16

IND, DE, 20

IND, MU, 18

Now lets say your goal is to get the Sum by each country and then display the average in a text box

Avg(Aggr(Sum(Population), Country, State))

the above will give you (10+16) + (20 + 18) divided by 2 => 32

Re: Nested expression

vvvvvvizard — Fri, 14 Jul 2017 12:17:18 GMT

Try just Avg(x)

Re: Nested expression

effinty2112 — Fri, 14 Jul 2017 12:21:49 GMT

Hi Sunny,

Maybe Avg(Aggr(Sum(Population), Country, State))

= 32 ?

Re: Nested expression

effinty2112 — Fri, 14 Jul 2017 12:30:06 GMT

Hi Siddharth,

Sunny's dead right (as usual) suggesting use of the Aggr function.

Consider this straight table:

Country	Sum(Population)
	64
IND	38
US	26

If we change the total mode to Avg in the expressions tab we get this

Country	Sum(Population)
	32
IND	38
US	26

The straight table gives you the answer you want, 32.

The expression

Aggr(sum(Population), Country)

can be thought of somehow creating an in-memory straight table with dimension Country and expression sum(Population).

If we wrap this in Avg() :

Avg(Aggr(sum(Population), Country))

that's like setting the Total Mode of a straight table to Avg and this will return the answer we're looking for in a textbox.

Aggr is incredibly useful!

cheers

Andrew

Re: Nested expression

arvind_patil — Fri, 14 Jul 2017 12:32:50 GMT

Hi Sidharth,

If you want it to group by country wise then use below exp:

Maybe Avg(Aggr(Sum(Population), Country))

If you want it to normal Avg then use below exp:

Avg(population)

Thanks,

Arvind Patil

Re: Nested expression

sunny_talwar — Fri, 14 Jul 2017 12:38:21 GMT

Yes you are right