Average of 3 in Total

[Kirsten]

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

[lironbaram]

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]

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]

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]

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

[lironbaram · Accepted solution]

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]

@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]

@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