# Qlik Sense App Development

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Partner

## Calculate avg of avg with aggr

Hey guys,

My data model looks like:

datebrandmypricecompprice
2018-01-01B11,0001,000
2018-01-01B21,0001,111
2018-01-01B31,0001,222
2018-01-01B41,0001,333
............

Lets says that B1 is my own brand. Each row contains my brand and the competing brand price. (1st row the competing price is my own price). I am trying to have a bar chart with a bar for each brand and their distance to my own price.

I am trying to populate the following table:

The B2, B3, B4 column is the average of the distances to my own price:

```Avg(myprice) - Avg({\$<[brand]={'B2'}>}compprice)

```

I would like a column with the average of the averages. The correct number is shown in the column "Desired" and is just the result of averaging the values of the three columns B2, B3, B4.

But I am trying to use this in a chart as well and I can not get my expression to yield the same result. I am using this expression in the "Test Avg of Avg" column:

```Avg(Aggr(Avg(myprice) - Avg(compprice), date, brand))

```

Appreciate any feedback.

Thanks

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Calculate avg of avg with aggr

This seems to work

Avg(Aggr(Avg(myprice) - Avg({<brand -= {'b1'}>} compprice), [date.autoCalendar.YearMonth], brand))

5 Replies
Highlighted
MVP

## Re: Calculate avg of avg with aggr

This seems to work

Avg(Aggr(Avg(myprice) - Avg({<brand -= {'b1'}>} compprice), [date.autoCalendar.YearMonth], brand))

Highlighted
Partner

## Re: Calculate avg of avg with aggr

Of course, makes sense

Needed to excluded the B1 row.

Thanks!

Highlighted
Partner

## Re: Calculate avg of avg with aggr

I have extended my test case a bit with the concept of a product. And now it does not work anymore.

Which now gives this result:

I have attached the new test app as well.

Right now it does not matter that we aggr over all the products, but at some point I need to separate into each product, e.g. product={"P1"}.

Highlighted
MVP

## Re: Calculate avg of avg with aggr

May be this

Avg(Aggr(Avg(TOTAL <[date.autoCalendar.YearMonth]>  myprice) - Avg({\$<brand-={'B1'}>}compprice), [date.autoCalendar.YearMonth], brand, product))

I guess based on the data, the expression might need to change a little bit....You can also read here about the challenges with Average

Average – Which average?

Highlighted
Partner

## Re: Calculate avg of avg with aggr

Once again thank you