11 Replies Latest reply on Jun 9, 2016 6:29 AM by Steve Taylor

# How to increase the value of your top N by x%

Hi,

I have data on products which fit into categories, and a worksheet than displays them by category and descending sales. What I want to achieve is to answer "if I increase sales on my top N products within each category by X%, what will be the extra benefit?"

Now its easy enough to do the X% bit as its just a parameter but how can I work out the top N in a calculated field so that it goes

if [product in top N for sales within category] then [sales]*(1+[X percentage increase])

else [sales]

end

How do i do thefirst bit? I've attached a basic version of what I am trying to achieve, using the top two but you will notice I've had to hard-code the top two within each category- not ideal.

Any help greatly appreciated,

Sam

• ###### 1. Re: How to increase the value of your top N by x%

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

1 of 1 people found this helpful
• ###### 2. Re: How to increase the value of your top N by x%

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%

Hi 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

1 of 1 people found this helpful
• ###### 4. Re: How to increase the value of your top N by x%

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!

Sam

• ###### 5. Re: How to increase the value of your top N by x%

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.

• ###### 6. Re: How to increase the value of your top N by x%

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%

You're welcome!

I learnt something myself with this one so it's win-win.

• ###### 8. Re: How to increase the value of your top N by x%

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

• ###### 9. Re: How to increase the value of your top N by x%

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%

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%

Nice one!