Calculate avg of avg with aggr

[rasmusnielsen]

Hey guys,

My data model looks like:

date	brand	myprice	compprice
2018-01-01	B1	1,000	1,000
2018-01-01	B2	1,000	1,111
2018-01-01	B3	1,000	1,222
2018-01-01	B4	1,000	1,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

[sunny_talwar · Accepted solution]

This seems to work

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

[rasmusnielsen]

Of course, makes sense

Needed to excluded the B1 row.

Thanks!

[rasmusnielsen]

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"}.

[sunny_talwar]

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?

[rasmusnielsen]

Once again thank you

Very interesting read in that linked article!