Solved: How to show sale of products below overall average - Qlik Community

Dayna · ‎2010-06-10

Dear all,

I am trying to decide the best route to show sale of products below the overall average, the overall average may be of customer or product (not yet decided), what would be the best method to achieve this?

Any help that you could provide would be most appreciated.

Kind Regards,
Dayna

Anonymous · ‎2010-06-10

I have tried to paste from excel but it didn't work.

You can use the formula below in a pivot table with cust and prod dimension:

if(avg(TOTAL <prod> value)>=avg( value),avg( value))

View solution in original post

Anonymous · ‎2010-06-10

Can you be more specific about it? Upload a document.

Dayna · ‎2010-06-10

I haven't got a document to upload yet, as I'm trying to work out how is best to achieve this result. Let's say, the following data:

Customer Amount of Product A Amount of Product B

A 20 25

B 20 35

C 20 2

A 5 40

D 5 6

So for the above the average is 14 for Product A, so I'd like to show Customer A and D, and I would like to show for Product B the average is 21.6 so I would like to show customer C and D.

Hope this clarifies what I am trying to do.

Regards,
Dayna

Anonymous · ‎2010-06-10

<body> <col width="98"></col> <col width="98"></col> <col width="140"></col> <col width="98"></col> <col width="160"></col> <tr> <td width="98" class="xl22" height="17">prod</td> <td width="98" class="xl23">cust</td> <td width="140" class="xl24">avg(TOTAL <prod> value)</td> <td width="98" class="xl24">avg( value)</td> <td width="160" class="xl24">if(avg(TOTAL <prod> value)>=avg( value),avg( value))</td> </tr> <tr> <td class="xl24" height="17">a</td> <td class="xl24">a</td> <td align="right" class="xl24">14</td> <td align="right" class="xl24">12.5</td> <td align="right" class="xl24">12.5</td> </tr> <tr> <td class="xl24" height="17">a</td> <td class="xl24">b</td> <td align="right" class="xl24">14</td> <td align="right" class="xl24">20</td> <td class="xl24"> </td> </tr> <tr> <td class="xl24" height="17">a</td> <td class="xl24">c</td> <td align="right" class="xl24">14</td> <td align="right" class="xl24">20</td> <td class="xl24"> </td> </tr> <tr> <td class="xl24" height="17">a</td> <td class="xl24">d</td> <td align="right" class="xl24">14</td> <td align="right" class="xl24">5</td> <td align="right" class="xl24">5</td> </tr> <tr> <td class="xl24" height="17">b</td> <td class="xl24">a</td> <td align="right" class="xl24">21.6</td> <td align="right" class="xl24">32.5</td> <td class="xl24"> </td> </tr> <tr> <td class="xl24" height="17">b</td> <td class="xl24">b</td> <td align="right" class="xl24">21.6</td> <td align="right" class="xl24">35</td> <td class="xl24"> </td> </tr> <tr> <td class="xl24" height="17">b</td> <td class="xl24">c</td> <td align="right" class="xl24">21.6</td> <td align="right" class="xl24">2</td> <td align="right" class="xl24">2</td> </tr> <tr> <td class="xl24" height="17">b</td> <td class="xl24">d</td> <td align="right" class="xl24">21.6</td> <td align="right" class="xl24">6</td> <td align="right" class="xl24"> 6 </td> </tr> </body>

Anonymous · ‎2010-06-10

I have tried to paste from excel but it didn't work.

You can use the formula below in a pivot table with cust and prod dimension:

if(avg(TOTAL <prod> value)>=avg( value),avg( value))

Dayna · ‎2010-06-10

I think that should do it! Thank you!