
1. Re: How to increase the value of your top N by x%
Steve Taylor May 31, 2016 5:34 AM (in response to Sam Wright)Hello Sam,
I've attached a workbook with a solution using a table calc Index() to determine the rank of each product within the category and used a parameter to determine your top N and a calc to apply the percentage increase to only those top N products.
let me know if it doesn't solve your problem
Thanks
Steve

top n adding solution.twbx 60.0 KB


2. Re: How to increase the value of your top N by x%
Sam Wright May 31, 2016 6:27 AM (in response to Steve Taylor)That's great thanks. How would I do it so that I get a summary sheet that tells me the total increase in amount across all categories? Becuase it currently only works on that sheet where they're all nested. Is theis where a level of detail calc would come in?
Thanks again

3. Re: How to increase the value of your top N by x%
Steve Taylor May 31, 2016 6:51 AM (in response to Sam Wright)1 of 1 people found this helpfulHi Sam,
So you want to be able to remove product from the list and for each category have the SUM(add increase to top N) value?
The method I used does require the products to be in the view, let me have a think of another solution and I'll get back to you.
Steve

4. Re: How to increase the value of your top N by x%
Sam Wright May 31, 2016 7:12 AM (in response to Steve Taylor)Hi Steve,
I just want a sheet with one number in it which is the new total of all the products added together after the percentage increase, a tricky one!
Thanks again for your help,
Sam

5. Re: How to increase the value of your top N by x%
Steve Taylor May 31, 2016 8:53 AM (in response to Sam Wright)Hello Sam
I received some help from a colleague Carl Slifer who provided a solution using Window calculations and setting the partitioning of those calculations correctly. hopefully I can do his explanation justice!
I created a new field called Overall amount after N increase shown below. I've added this to sheet 3 so you can see it correctly adding up the total of the "add increase to top N" field.
To be able to use this without Category or Product in the view we need to make sure the default table calc is set to address those fields which is done by clicking on the blue "Default Table Calculation" link then choosing "advanced" from the Compute using menu which brings up the Partitioning and Addressing boxes shown below:
You need to first add Category then Product using the > arrows then click OK on all three windows.
In sheet 4 I've added Category and Product to the detail shelf, set the marks to Text and as we've sorted product in previous sheets I've also sorted it here by right clicking and choosing to sort descending by Amount and Sum. You can hover over the ABCs to show each represents a category and product.
Adding the new "Overall amount after N increase" calc to the text shelf shows the same value for all items. As that's the figure you want to show you only need to see it once. To do this we use another table calc FIRST() == 0 which basically gives a True/False result if the item in a partition is the first one. This needs to be addressed similarly to the window_sum above so you need to select default table calculation  advanced  addressing Category and Product as you did before.
If you add this field to the filter shelf and select True you get your total amount given a % increase of N applied to the top N products in each category.
Workbook attached, let me know if that helps! And if it does I'll pass your thanks onto Carl.

top n adding solution v2.twbx 91.4 KB


6. Re: How to increase the value of your top N by x%
Sam Wright Jun 1, 2016 1:57 AM (in response to Steve Taylor)You pair of absolute geniuses! Thank you for the time and effort you put into this, it's really helped me out.
Sam

7. Re: How to increase the value of your top N by x%
Steve Taylor Jun 1, 2016 2:25 AM (in response to Sam Wright)You're welcome!
I learnt something myself with this one so it's winwin.

8. Re: How to increase the value of your top N by x%
Sam Wright Jun 9, 2016 3:09 AM (in response to Sam Wright)Hi again Steve,
I've tried adding a "difference" sheet in addition to the "increase" so if sales went up from £1000 to £1100 i want a total underneath with £100. If attached my attempt but it doesn't seem to be calculating correctly, what am I doing wrong?
Many thanks,
Sam

top n adding solution v2.twbx 81.4 KB


9. Re: How to increase the value of your top N by x%
Steve Taylor Jun 9, 2016 4:12 AM (in response to Sam Wright)Hi Sam,
I'm busy this afternoon but could take a look at this tomorrow morning if that's not too late for you?
Steve.

10. Re: How to increase the value of your top N by x%
Sam Wright Jun 9, 2016 4:47 AM (in response to Steve Taylor)I think i've actually figured it out! My actual dataset had more fields in it like site and I'd had them in the wrong part of the calculation

11. Re: How to increase the value of your top N by x%
Steve Taylor Jun 9, 2016 6:29 AM (in response to Sam Wright)Nice one!