9 Replies Latest reply on Jan 21, 2014 11:02 AM by Matt Lutton

    Top N sort on a rolling percentage

    Julia Hennelly

      I am close to the solution I need but just can not get the final pointers.  I have a workbook that shows overall and first year turnover by department.  I have two issues that I need to finalize:

       

      1. Some departments have no turnover in the time period displayed and so I do not want to show those departments.  I can exclude departments with no turnover during the entire period - using the include/exclude calculated field (Exclude_Zero Departments) but need to exclude when there are 0 terms in the time period displayed which is controlled by the user.

       

      2.

      • I would like to sort the departments (top N) by the maximum average overall turnover in the time period displayed.  So I need a field that calculates average overall turnover for the time period displayed.
      • I then need a field that ranks by this maximum field - trying to use rank but not having much success.
      • I then need a top N based on that ranking that changes as filters are changed.

       

      I am attaching a sample workbook.  Any ideas or pointers would be awesome.

       

      Thanks,

      Julia