Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
siddharthsoam
Partner - Creator II
Partner - Creator II

Nested expression

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
sunny_talwar

You will need to add Aggr() in between them

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

siddharthsoam
Partner - Creator II
Partner - Creator II
Author

Can you please elaborate listofdimensions by a working example

sunny_talwar

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

vvvvvvizard
Partner - Specialist
Partner - Specialist

Try just Avg(x)

effinty2112
Master
Master

Hi Sunny,

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


= 32 ?

effinty2112
Master
Master

Hi Siddharth,

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

Consider this straight table:

Country Sum(Population)
64
IND38
US26

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

Country Sum(Population)
32
IND38
US26

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

arvind_patil
Partner - Specialist III
Partner - Specialist III

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

sunny_talwar

Yes you are right