7 Replies Latest reply on Jan 8, 2019 6:11 PM by tian.yang.4

# Calculations for Average of Max 4 Week Sales

Hello -  I am trying to calculate the average of the highest for weeks of the year. My data set has 52 weeks of data for 15 categories and I am looking to find the average of the highest 4 weeks, they are not always sequential weeks. It could be Week 10, 22, 21 and 52.

It is the same function as Large and Small in Excel - but I cannot find a way to replicate it. The number of weeks to calculate I would like to be a dynamic parameter.

I have tried Ranking and Window_Avg - but you have to have all of the weeks showing and I am just looking for one number.

• ###### 1. Re: Calculations for Average of Max 4 Week Sales

HI Morgan

This is what you want?

i don't get your logic about 15 categories (if you want to rank by profit for each category i need to change the compute using direction)so I just calculate the overall profit.

• ###### 2. Re: Calculations for Average of Max 4 Week Sales

No it looks like this

I am able to separate out the top 6 weeks - but it wont give me the average -  just the sum total.

I need to be able to average the top 6 weeks - which are not always the same for each category.

• ###### 3. Re: Calculations for Average of Max 4 Week Sales

can you share the workbook?

• ###### 4. Re: Calculations for Average of Max 4 Week Sales

See Attached - I need it to display just a single number so I don't have to display the weeks.

• ###### 5. Re: Calculations for Average of Max 4 Week Sales

Hi morgan,

I can't figure this out and in my opinion I can only get top X by rank(or index etc..) which is a table calculation and table calculation's calculation order is too low to calculate more. and that might be the reason why here is the same for sum and average.

Maybe another expert has better solution.

Zhouyi Zhang

YT

• ###### 6. Re: Calculations for Average of Max 4 Week Sales

Hi, Morgan

The top N is for each department or overall in your case since you mentioned you just need one number?

ZZ

• ###### 7. Re: Calculations for Average of Max 4 Week Sales

Hi

I finally make it happen but a little bit complex.

As I said before, table calculation cannot be used, so I used LOD to distinguish the top 1-5.

Finally view: