Skip to main content

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 
Search instead for 
Did you mean: 
Kirsten
Creator II
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
lironbaram
Partner - Master III
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  

View solution in original post

7 Replies
lironbaram
Partner - Master III
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))

Kirsten
Creator II
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.

lironbaram
Partner - Master III
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 ?

 

Kirsten
Creator II
Creator II
Author

Hi @lironbaram , the average of 3 markets should replace the current total 

lironbaram
Partner - Master III
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  

Kirsten
Creator II
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.

Kirsten
Creator II
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