4 Replies Latest reply on Jun 2, 2016 12:21 PM by Joe Oppelt

    Creating a staggered double Date Range determined by Category using the same Data Source

    Ford Reid

      I know the title may sound confusing but what I'm trying to accomplish is to visualize a Gannt Bar Graph for different Infection Categories over a Rolling 12 Month period with a 1 month delay depending on the Category name.


      There is a main Category named HAI Type with 6 categories and 17 sub-categories.  What I want is the Graph to display the latest 12 months for 4 0f the sub-categories, but have a 1 month delay for the rest and show the previous 12 months prior to that.

      I have the Month filter set to Relative Dates showing the last 12 months with a Relative Anchor to April 2016.

      Date Range.bmp

      I've tried writing calculated fields to subtract 1 month from the current 12 but it doesn't seem to take any effect.  SSI is the category I want to delay by 1 month to the previous 12 rolling months.  Summary YM is the date field used for the date filter.  SIR Calc is a separate calculate field that runs based off an aggregated Numerator and Denominator:

      Attempt 1:

      if attr([HAI Type]) = "ssi" and datediff('month',attr([Summary YM]),today())<=12

      and datediff('month',attr([Summary YM]),today())>=1

      then [SIR Calc]  end


      Attempt 2:

      if [HAI Type] = "ssi" then ([Summary YM]-1)

      ELSE [Summary YM] END


      I've also tried Start and End Date Parameters too but the calculated field to work with the parameter doesn't like the Categorical Clause even with ATTR() applied.

      I've attached a sample packaged workbook for additional reference.

      If anyone knows of any tips or hints that I'm overlooking I would greatly appreciate the assistance!