Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Dayna
Creator
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
adrian_costar
Contributor III
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))

View solution in original post

5 Replies
adrian_costar
Contributor III
Contributor III

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

Dayna
Creator
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

adrian_costar
Contributor III
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>

adrian_costar
Contributor III
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))

View solution in original post

Dayna
Creator
Creator
Author

I think that should do it! Thank you!