Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

rasmusnielsen
New Contributor III

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

Screen Shot 2018-08-15 at 15.41.04.png

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:

Screen Shot 2018-08-15 at 15.33.11.png

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

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

Re: Calculate avg of avg with aggr

This seems to work

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

rasmusnielsen
New Contributor III

Re: Calculate avg of avg with aggr

Of course, makes sense

Needed to excluded the B1 row.

Thanks!

rasmusnielsen
New Contributor III

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.

Screen Shot 2018-08-15 at 17.21.53.png

Which now gives this result:

Screen Shot 2018-08-15 at 17.22.36.png

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

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?

rasmusnielsen
New Contributor III

Re: Calculate avg of avg with aggr

Once again thank you

Very interesting read in that linked article!

Community Browser