4 Replies Latest reply on Jan 15, 2018 10:11 AM by Tim Cady

    Grouping overlapping dates

    Tim Cady

      Hi all,

       

      I'm trying to create 2 groups based on organizations fiscal year dates. Here is the calculated field I have created...

       

      IF [Call Date] > #12-25-15# AND [Call Date] < #12-24-17# THEN '2016 - 2017'

      ELSEIF [Call Date] > #12-24-16# AND [Call Date] < #12-23-18# THEN '2017 - 2018'

      ELSE NULL

      END

       

      Problem is, the 2017 call dates are only appearing as part of 2016 - 2017. Does anyone have thoughts on a solution? Is a calculated field even the best way to do this?

        • 1. Re: Grouping overlapping dates
          Matthew Theis

          Hey Tim Cady   Are you sure you don't want:

           

          IF [Call Date] > #12-25-15# AND [Call Date] < #12-24-16# THEN '2015 - 2016'

          ELSEIF [Call Date] > #12-24-16# AND [Call Date] < #12-23-17# THEN '2016 - 2017'

          ELSE NULL

          END

           

          Also, it looks like this will be off a day every year.  Are you sure you don't want to have the FY go from 25Dec15-25Dec16?

           

          Matthew

          • 2. Re: Grouping overlapping dates
            Tim Cady

            Hi Matthew,

             

            Thanks for the reply. The purpose of the groups is to compare data year-over-year. That's the reason I need 2017 represented in both groups.

             

            As for the date ranges themselves, my organizations fiscal year always ends on the second to last Saturday of the year. So, anything between 12/27/15 and 12/25/16 is part of 2016 and so on. The date ranges could probably be tweaked a day or two but my goal remains the same.

            • 3. Re: Grouping overlapping dates
              Michael Ye

              Tim,

               

              According to the 'Group' definition in Tableau, one member cannot be assigned to more than 1 group. That means, if one day is assigned to 2016-2017 group, it cannot be assigned to 2017-2018 group. Therefore, In your case, year 2017 can only be assigned to one group. In order to include it in both 2016-2017 and 2017-2018, you need to assign "set" instead.

               

              Hope it helps.

               

              Thanks,

              3 of 3 people found this helpful
              • 4. Re: Grouping overlapping dates
                Tim Cady

                Thanks, that is helpful. It at least points me in the right direction.

                 

                One thing that immediately came up is that these set would need to be dynamic. For example, as I add data for 2018, I would need it automatically included in the set without me having to go in and add it each week.

                 

                EDIT: After a little research, I was able to create a condition for my set. I should be able to use these Sets as a filter once I combine them.