4 Replies Latest reply on Aug 22, 2016 7:41 AM by bill.merlavage

    Display data in Quarters, but not true Quarters

    bill.merlavage

      I would like to display data in 3-month 'quarters', but not true quarters.

      For example, assuming TODAY() = 8/22/2016, I would like to group my data as following:

      5/1/2016 to 7/31/2016 = Q4

      2/1//2016 to 4/30/2016 = Q3

      11/1/2015 to 1/31/2016 = Q2

      8/1/2015 to 10/31/2016 = Q1

       

      Further, I would like to extend this logic to look at previous years.

      5/1/2015 to 7/31/2015 = Q4-prev

      2/1//2015 to 4/30/2015 = Q3-prev

      11/1/2015 to 1/31/2015 = Q2-prev

      8/1/2014 to 10/31/2014 = Q1-prev

      etc...

       

      I've tried using Bins, which almost worked but when spanning multiple years, Tableau creates separate a '0' grouping and a '12' grouping (which should be combined)

       

      I'm able to capture the yearly data using the following, which allows me to break ii into every increment except these 'quarters':

      DATETRUNC('month', DATEADD('month',-12, TODAY())) <= [Enc Date] AND [Enc Date] <= TODAY()

      DATETRUNC('month', DATEADD('month',-24, TODAY())) <= [Enc Date] AND [Enc Date] < DATETRUNC('month', DATEADD('month',-12, TODAY()))

      DATETRUNC('month', DATEADD('month',-36, TODAY())) <= [Enc Date] AND [Enc Date] < DATETRUNC('month', DATEADD('month',-24, TODAY()))

       

      A possible solution would be using Tableau's built-in Fiscal Year Start feature, but I need to have this set dynamically based on TODAY() rather than hard-coded.

       

      Does anyone have any suggestions?

      Thanks,

      Bill

        • 1. Re: Display data in Quarters, but not true Quarters
          Luciano Vasconcelos

          I'd build a dimension based in date field.

           

          For example, assuming TODAY() = 8/22/2016, I would like to group my data as following:

          5/1/2016 to 7/31/2016 = Q4

          2/1//2016 to 4/30/2016 = Q3

          11/1/2015 to 1/31/2016 = Q2

          8/1/2015 to 10/31/2016 = Q1

           

          Further, I would like to extend this logic to look at previous years.

          5/1/2015 to 7/31/2015 = Q4-prev

          2/1//2015 to 4/30/2015 = Q3-prev

          11/1/2015 to 1/31/2015 = Q2-prev

          8/1/2014 to 10/31/2014 = Q1-prev

           

          If [Enc Date]>=DATEADD('month',-4, TODAY()) and [Enc Date]<=DATEADD('month',-1, TODAY()) Then 'Q4'

          Else

          ....

           

          I'm not considering days. You need to adjust with datetrunc, but you build all of your quarters in the some calculation and use it to break your values.

          1 of 1 people found this helpful
          • 2. Re: Display data in Quarters, but not true Quarters
            bill.merlavage

            Thank you very much...this formula seems to be working:

            If     [Enc Date]>=DATETRUNC('month', DATEADD('month',-3, TODAY())) AND [Enc Date] <= TODAY() Then 'Q4'
            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-6, TODAY())) Then 'Q3'
            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-9, TODAY())) Then 'Q2'
            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-12, TODAY())) Then 'Q1'

            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-15, TODAY())) Then 'Q4'
            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-18, TODAY())) Then 'Q3'
            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-21, TODAY())) Then 'Q2'
            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-24, TODAY())) Then 'Q1'

            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-27, TODAY())) Then 'Q4'
            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-30, TODAY())) Then 'Q3'
            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-33, TODAY())) Then 'Q2'
            ELSEIF [Enc Date]>=DATETRUNC('month', DATEADD('month',-36, TODAY())) Then 'Q1'

            END

            1 of 1 people found this helpful
            • 3. Re: Display data in Quarters, but not true Quarters
              Luciano Vasconcelos

              You need to suffix as you did before: -prev.

              If not you'll mix periods.

              Is it ok for you?

              1 of 1 people found this helpful
              • 4. Re: Display data in Quarters, but not true Quarters
                bill.merlavage

                Noted.  Actually my plan is to give the quarters more dynamic names...such as 'Q4 ending: ' + str([some sort last day of the quarter date calc]), but I haven't figured that piece out yet.

                Thanks again for your help.