10 Replies Latest reply on Dec 1, 2016 6:33 AM by Amit Narkar

    Finding Saturday between two dates?

    Kranthi Kembasaram

      Hi Team,

       

      We are struck with this requirement, can we find a Saturday between two date columns??

      I have two date columns named lower_date and upper_date , in between these two dates want to find out the occurrence of saturday.

       

      Anyone for pointers or solution that we can achieve in tableau.

       

      Thanks,

      KK

        • 1. Re: Finding Saturday between two dates?
          Chris Chalmers

          Hey Kranthi,

           

          Here's a calculation that will give you the first Saturday on or after the lower date:

          first-saturday-after-lower-date.PNG

           

          Building on that, this will give you the first Saturday that occurs between the lower and upper dates (both inclusive), or null if no Saturday appears between them:

           

          first-saturday-between-dates.PNG

           

          And finally, this will give you the only Saturday that appears between the lower and upper dates (both inclusive), or null if there is not exactly one Saturday in the range.

           

          single-saturday-between-dates.PNG

           

          Let me know if that helps!

           

          -Chris Chalmers

          • 2. Re: Finding Saturday between two dates?
            Amit Narkar

            Hi Kranthi

             

            You can start with something as checking if Min Date and Max Date as Saturday.

             

            e.g.

            Is Min Date Saturday= if datepart('weekday',[Min Date])=7 then 1 else 0 end

            Is Max Date Saturday= if datepart('weekday',[Max Date])=7 then 1 else 0 end

             

            No of Saturdays=

            if [is Min date Saturday]+[is Max Date Saturday]=0

            AND datediff('day',[Min Date],[Max Date])>6 then int(datediff('day',[Min Date],[Max Date])/7)

            elseif [is Min date Saturday]+[is Max Date Saturday]>0 AND datediff('day',[Min Date],[Max Date])<=7

            then ([is Min date Saturday]+[is Max Date Saturday])

            elseif [is Min date Saturday]+[is Max Date Saturday]>0  AND datediff('day',[Min Date],[Max Date])>7

            then 1+int(datediff('day',[Min Date],[Max Date])/7)

            else 0

            end

             

            Saturday.png

             

            You can probably test it if there are any further scenarios not handled.

            • 3. Re: Finding Saturday between two dates?
              yogesh sawant

              Hello Kranthi,

               

              please check following solution,

               

              [SSSSSSSSS] is the lower date and END_DATE is the upper date.

               

              We create Start date using lower date as follows.

               

               

              Now difference between two dates will give no of saturdays by following formula.

               

              Let me know if this solves your purpose.

              2 of 2 people found this helpful
              • 4. Re: Finding Saturday between two dates?
                yogesh sawant

                Hello Kranthi,

                 

                Another solution you can look upon is creating date table in your database or in a excel as a datasource.

                 

                Calendar Table could be as following image, you can add any one time calculation needed in this table. Now by using this with blending or in joins you can find out date related information very easily. This table can also be used in future requirements as well.

                 

                -Yogesh

                • 5. Re: Finding Saturday between two dates?
                  Kranthi Kembasaram

                  Thanks for the solution Chris, it works like a charm, but we can check this only for a week only right.

                  But i got a scenario where in Lower date=10/18/16 and Upper date=10/31/16 , so between these two dates we are getting two saturdays.

                   

                  But with the above mentioned logic , this would show as null as the condition doesn't satisfy. But literally speaking there are two saturday occurrences between lower and upper dates. Any idea?

                  • 6. Re: Finding Saturday between two dates?
                    Chris Chalmers

                    Can you please describe the visualization you're trying to create? I fear that what you are trying to do may be impossible. In general you can't create a calculation that returns multiple values for a single input. Perhaps I or someone else can help you approach the problem a different way if you tell us what you are trying to visualize. And perhaps someone will come along to tell me I'm wrong and teach us both something, also a possibility .

                     

                    If you're just trying to get the number of Saturdays between the upper and lower date, the following calculation will do the trick. It uses the calculation from earlier and again assumes both dates are inclusive:

                     

                    num-saturdays-between-dates.PNG

                    • 7. Re: Finding Saturday between two dates?
                      Amit Narkar

                      have you tried the solution i suggested?

                      • 8. Re: Finding Saturday between two dates?
                        Kranthi Kembasaram

                        Amit,

                         

                        Sorry for the late response, as it was thanksgiving break for a week.

                         

                        I tried your solution , but i have questions on the solution that when my lower and upper dates are as below the result of no of saturdays should be two, but as per your solution when difference between 2 dates is 14 days only we get as 2, where as other dates it shows 1.

                         

                        But if you see the calendar it got 2 saturdays.

                         

                        example :-

                        lower_date           upper_date    No. of saturdays

                        10/18/16               10/31/16          2

                        10/19/16               10/31/16          2

                        10/20/16               10/31/16          2

                        10/22/16               10/31/16          2

                         

                        PS:- My lower & upper date might have occurrence of 3 saturdays as well, so how can we handle it.

                         

                        Thanks,

                        KK

                        • 9. Re: Finding Saturday between two dates?
                          Kranthi Kembasaram

                          Chris,

                           

                          My requirement is like for getting the sales information for weekends, so i need to get how many saturdays fall between lower & upper date , so the possibility might be there might be 2 or 3 saturdays falling between lower & upper.

                           

                          example :-

                          lower_date           upper_date    No. of saturdays

                          10/18/16               10/31/16          2

                          10/19/16               10/31/16          2

                          10/20/16               10/31/16          2

                          10/22/16               10/31/16          2

                           

                          Thanks,

                          KK

                          • 10. Re: Finding Saturday between two dates?
                            Amit Narkar

                            Hi Kranthi,

                             

                            Could you try with this?

                             

                            if datediff('day',[Min Date],[Max Date])<6 AND [is Max Date Saturday]+[is Min date Saturday]>0

                            then [is Max Date Saturday]+[is Min date Saturday]

                            elseif datediff('day',[Min Date],[Max Date])<6 AND [is Max Date Saturday]+[is Min date Saturday]=0

                            then datediff('week',[Min Date],[Max Date])

                            elseif datediff('day',[Min Date],[Max Date])>6 AND [is Max Date Saturday]+[is Min date Saturday]=2

                            then datediff('week',[Min Date],[Max Date])-1+[is Max Date Saturday]+[is Min date Saturday]

                            else

                            datediff('week',[Min Date],[Max Date])

                            end

                             

                             

                            is Max Date Saturday

                            if datepart('weekday',[Max Date])=7 then 1 else 0 end

                             

                             

                            is Min date Saturday

                            if datepart('weekday',[Min Date])=7 then 1 else 0 end

                             

                            I have attached workbook for your reference.