1 2 Previous Next 28 Replies Latest reply on Jan 21, 2016 8:01 AM by Anthony Bour

    Setting Date Period of 27 Date Increments

    Anthony Bour

      Hi,

      I am new to Tableau and this in my first post - so apologies if this is posted in the wrong forum.

       

      I am having trouble working out how to create or set my date field so it displays at 27 day increments.

       

      Example:

      Period 1:     05.Jan.16 - 01.Feb.16

      Period 2:     02-Feb-16 - 29-Feb-16

      Period 3:     01.Mar.16 - 28.Mar.16

      Period 4:     01.Mar.16 - 28.Mar.16

      etc...

       

      I have searched the training guides but I cant find an answer.  I get the feeling this a pretty easy fix as this must be required by many companies, any help would be greatly appreciated.

       

      Thanks

      Anthony

        • 1. Re: Setting Date Period of 27 Date Increments
          Mark Fraser

          I am new to Tableau and this in my first post - so apologies if this is posted in the wrong forum.

          Hi Anthony

           

          No problem, just moved it for you.

           

          With regard to your question - This is quite possible in Tableau, its just how you want to do it.

          You want a user to select a date, and then for the period to reflect the selected date + 27days?

          or something else?

           

          Your going to need either

          DATEDIFF('day',[1st Date],[2nd Date])

          or

          DATEADD('day',27,[Date])

           

          and maybe IF function.

           

          If you could provide a little more detail, and ideally an example I can help you better.

           

          Cheers

          Mark

          • 2. Re: Setting Date Period of 27 Date Increments
            Anthony Bour

            Hi Mark,

            Thanks for your reply,  I have a shipment departure date  I am trying to build a dashboard for my client, but they do not operate using calendar months – instead they want to see data based on 27 day periods.  The start date I want to use is 06.Jan.15 and I want to create a period for each 27 day segment.

             

            I think somehow I need to group all dates which fall into each period, I did try using an IF formula but this doesn’t seem very practical as I would need to constantly add new date ranges as the months/years go by.

             

            So in summary, if I have a shipment which departing on 10.Jan.16 I want related data to display under “2016 Period 1”, if I have a shipment departing on 02.Feb.16 I want this data to display under “2016 Period 2”.

             

            Hope this makes sense.

             

            Regards

            Anthony

            • 3. Re: Setting Date Period of 27 Date Increments
              Norbert Maijoor

              Anthony,

               

              Not very sofisticated but could work.....

               

               

              if [Date]>=#5-1-2016# and [Date]<=#5-1-2016#+27 then "Period 1"

              elseif [Date]>#5-1-2016#+27 and [Date]<=#5-1-2016#+(27*2) then "Period 2"

              elseif [Date]>#5-1-2016#+(27*2) and [Date]<=#5-1-2016#+(27*3) then "Period 3"

              elseif [Date]>#5-1-2016#+(27*3) and [Date]<=#5-1-2016#+(27*4) then "Period 4"

              elseif [Date]>#5-1-2016#+(27*4) and [Date]<=#5-1-2016#+(27*5) then "Period 5"

              elseif [Date]>#5-1-2016#+(27*5) and [Date]<=#5-1-2016#+(27*6) then "Period 6"

              elseif [Date]>#5-1-2016#+(27*6) and [Date]<=#5-1-2016#+(27*7) then "Period 7"

              elseif [Date]>#5-1-2016#+(27*7) and [Date]<=#5-1-2016#+(27*8) then "Period 8"

              END

              • 4. Re: Setting Date Period of 27 Date Increments
                Mark Fraser

                Good work Norbert, I cant (yet) think of anything much better...

                 

                The problem is 'holding the +27 data value and reusing it?!

                Norbert's method circumvents that problem by constantly using the same starting point.

                • 5. Re: Setting Date Period of 27 Date Increments
                  Norbert Maijoor

                  Mark,

                   

                  Let's wait.....you never know if there is a briljant kid on the block coming up with a "nifty" solution;).....but up till now I like working with a "fixed" reference point.

                  • 6. Re: Setting Date Period of 27 Date Increments
                    Anthony Bour

                    Thanks both, I was hoping for a more automated solution but this does give me the split I am looking for.

                     

                    Appreciate your help on this.

                     

                    Regards

                    Anthony

                    • 7. Re: Setting Date Period of 27 Date Increments
                      Mark Fraser

                      Hi Anthony

                       

                      There maybe a better way, I never say never on this forum (learnt my lesson), but I can't think of it....

                      As mentioned the crux of the issue with automating is holding the +27 days date and then reusing it, for the next calc and then using that new date and so on... < Norbert cleverly circumvented this by keeping a single fixed date only.

                      In Excel we could use $ to fix the cell but that isn't available in Tableau.

                       

                      Someone may come along with a super clever way... you never know!

                       

                      Cheers

                      Mark

                      • 8. Re: Setting Date Period of 27 Date Increments
                        Jonathan Drummey

                        I've got one...working on it...

                        • 9. Re: Setting Date Period of 27 Date Increments
                          Shawn Wallwork

                          I can get you this:

                           

                          Using: DATEPART('dayofyear', [Order Date])

                           

                          Then binning the results with a bin size of 27. And then changing all the aliases to Period X

                           

                          What version of Tableau are you using?

                           

                          --Shawn

                          1 of 1 people found this helpful
                          • 10. Re: Setting Date Period of 27 Date Increments
                            Shawn Wallwork

                            How do you want to deal with the year change over. Start over at 1, or continue with count? For instance 4 days in December plus 23 days in January.

                             

                            --Shawn

                             

                            EDIT: Here's what it would look like if we start at 1 each year:

                            • 11. Re: Setting Date Period of 27 Date Increments
                              Norbert Maijoor

                              Mark Fraser

                               

                              Warned you! The most briljant kid on the block provided something "nifty";)

                              • 12. Re: Setting Date Period of 27 Date Increments
                                Yuriy Fal

                                Hi Anthony,

                                 

                                You could use a calc field like this for a [Period #] :

                                 

                                floor( datediff('day', [Start Date Parameter], [Ship Date]) / 28 ) + 1

                                 

                                where [Start Date Parameter] could be a Parameter

                                with a default value of January 5, 2016 in your case.

                                 

                                Hope this could help.

                                 

                                Yours,

                                Yuri

                                1 of 1 people found this helpful
                                • 13. Re: Setting Date Period of 27 Date Increments
                                  Jonathan Drummey

                                  I think you mean that the periods are 28 days, not 27, since all the ones you list are 28 days, is that correct?

                                   

                                  The way I approached this problem is that it's one about binning the dates into 28 day periods and making sure that the period/bin numbers are accurate.

                                   

                                  Here's a calc:

                                   

                                  IF [Date] >= #2016-01-05# THEN

                                      INT((DATEDIFF('day',#2016-01-05#,[Date])) / 28) + 1

                                  ELSE

                                      INT(DATEDIFF('day',#2016-01-04#,[Date]) / 28) + -1

                                  END

                                   

                                  It uses the notion of an epoch date set to 2016-01-05, then bins each 28 day period. In order to get the periods prior to the 1st period and set those as negative numbers there's a 2nd calc in the ELSE part. Finally there's no 0th period, so the bin calcs use +1 and -1.

                                   

                                  I used SIZE() in the workbook with a compute using on the Date to validate that every period is 28 days (other than the first and last in the sample data that I created). Here's the view:

                                   

                                   

                                   

                                   

                                  v9.0 workbook is attached.

                                   

                                  Jonathan

                                  1 of 1 people found this helpful
                                  • 14. Re: Setting Date Period of 27 Date Increments
                                    Mark Fraser

                                    ha-ha, it always happens to me Norbert!!

                                    I learned some time back - never say never in this forum, its like setting a challenge!

                                    But is not so bad... the OP gets what they need and we learn something new 

                                     

                                    ...consistently amazed not only by the variety of techniques, tricks and hacks but by members willingness to help and share (a BIG thank you Shawn, Yuriy & Jonathan)

                                    1 2 Previous Next