2 Replies Latest reply on Jun 1, 2016 3:19 PM by Ford Reid

    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 an example packaged workbook for additional reference.

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