Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Average of 3 in Total

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

Kirsten

Creator II

2020-06-03
08:12 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

465 Views

1 Solution

Accepted Solutions

lironbaram

Partner - Master III

2020-06-04
07:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

398 Views

7 Replies

lironbaram

Partner - Master III

2020-06-03
10:14 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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))

- Tags:
- i

435 Views

Kirsten

Creator II

2020-06-03
10:31 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

431 Views

lironbaram

Partner - Master III

2020-06-03
10:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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 ?

425 Views

Kirsten

Creator II

2020-06-04
06:21 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

402 Views

lironbaram

Partner - Master III

2020-06-04
07:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

399 Views

Kirsten

Creator II

2020-06-04
08:06 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

388 Views

Kirsten

Creator II

2020-06-04
02:52 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

@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

355 Views