2 Replies Latest reply on Dec 23, 2016 5:57 AM by Corey Brown

    Action Filter for Rolling Time Period

    Corey Brown

      I was able to calculate one number for average@@@@@ employee count for Current Month, R3, R6, and R12 in one table.  See the image below.


      The logic behind this Employee Count calculation is as follows:



      if attr([Time Period]) = "Current Month" then COUNTD([Employee Number]) else

          if attr([Time Period]) = "R3" THEN WINDOW_SUM(SUM([Technician Count]), first()+1, 0)/3

          ELSEIF  attr([Time Period]) = "R6" then WINDOW_SUM(SUM([Technician Count]), FIRST()+1, 0)/6

          elseif  attr([Time Period]) = "R12" then WINDOW_SUM(SUM([Technician Count]), first()+1, 0)/12




      I would like the ability to drill down via action filter to show the employees that make up the Current Month, R3, R6, and R12 averages.  My problem arises when I click on the action filter for R12 for example, the destination sheet only shows me the first 6 months of the 12 months needed.  Likewise, when I click the R6 action, I only get the first 3 months of 6 months needed.  I think this problem occurs because I am using an IF statement to define my time periods, therefore the order of operations is what leaves me with the wrong number of months.  Is there any way around this?  Any help would be much appreciated!  Attached is an example workbook I put together in Tableau Version 10.