# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Creator

## How to show sale of products below overall average

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

1 Solution

Accepted Solutions
Contributor III

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

5 Replies
Contributor III

Creator
Author

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

Contributor III

<body><p> </p> <p><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 &lt;prod&gt;  </span>value)</td> <td width="98" class="xl24">avg( value)</td> <td width="160" class="xl24">if(avg(TOTAL &lt;prod&gt;  </span>value)&gt;=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"><br /></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"><br /></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"><br /></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"><br /></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"> <p>6</p> </td> </tr> </p> <p> </p></body>

Contributor III

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

Creator
Author

I think that should do it! Thank you!