# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for
Did you mean:  Creator II

## Average of 3 in Total

See attachment for example. QVD is attached.

Instead of having a Total value in my table, I would like to have the average of 3 countries (AFI, BNL, FRA) in this Total field. So for FCO72200421 it is (2.51+3.28+2.43)/3  = 3.57. So the 3.10 now  in Total will then be replaced by 3.57.

The expression for the numbers in the table (2.51, 2,77 etc) is:      Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])

The dimension name for AFI, BNL etc is: Market and for FCO72200421 is: FCO

1 Solution

Accepted Solutions  Partner - Master III

hi

this expression will keep the total value as the avg of those 3 markets , disregarding selections in the market field

``````if(Dimensionality()=0, avg({<Market={"FRA","AFI","BNL"}>}aggr({<Market={"FRA","AFI","BNL"}>}Sum({<Market={"FRA","AFI","BNL"}>}[Sum of Total Sum of Hours [ITM]]])/Sum({<Market={"FRA","AFI","BNL"}>}[Count of EQ ID]),FCO,Market)),
Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID]))``````

isn't it a little bit confusing to the users that it appears as total but it actually something different

7 Replies  Partner - Master III

hi

this expression will give you in the total the avg. of countries for each FCo

avg(aggr(Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID]),Market,FCO))  Creator II
Author

Thank @lironbaram  is there a way I can specify in the expression the markets I need the average from. I need to have the average from 3 markets only: BNL (=benelux), AFI (=Afrika), FRA (=France), not from all markets. And this average needs to be added to the total.  Partner - Master III

hi

so you want the table to display the expression as it is currently

and then in the total to have to total as it displayed currently plus the avg

or the avg of the 3 markets should replace the current total ?  Creator II
Author

Hi @lironbaram , the average of 3 markets should replace the current total  Partner - Master III

hi

this expression will keep the total value as the avg of those 3 markets , disregarding selections in the market field

``````if(Dimensionality()=0, avg({<Market={"FRA","AFI","BNL"}>}aggr({<Market={"FRA","AFI","BNL"}>}Sum({<Market={"FRA","AFI","BNL"}>}[Sum of Total Sum of Hours [ITM]]])/Sum({<Market={"FRA","AFI","BNL"}>}[Count of EQ ID]),FCO,Market)),
Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID]))``````

isn't it a little bit confusing to the users that it appears as total but it actually something different  Creator II
Author

@lironbaram  It works thanks! Yes it can be confusing, I'll add a clear note in the dashboard. It is just a work stream lead who wants to compare numbers to the 3 best  performing markets. He is adding all sorts of calculations in excel, which is easy there, but in Qliksense getting the same calculations is a bit more complicated.  Creator II
Author

@lironbaram  how do I get that value of the average 3 markets (which is in Total now)  in my code below, there where the 3.1 (in bold) is placed now? This is in a new graph/table

((1-Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])/3.1)*Sum([Sum of Total Sum of Cost [ITM]]]))*-1 Tags
Community Browser