8 Replies Latest reply on Oct 23, 2018 7:18 AM by Melody H

    Flexible Bi-weekly formula

    Melody H

      Hi guys,

       

      Currently I have a bi-weekly formula like this:

       

      'WK '+

      STR({ FIXED FLOOR( DATEPART('week',[Date],'Monday')/2):MIN(DATEPART('week',[Date],'Monday'))}) +

      " & " +

      STR({ FIXED FLOOR( DATEPART('week',[Date],'Monday')/2):MAX(DATEPART('week',[Date],'Monday'))})

       

      but in my tableau report it shows double week 30.

       

       

      The filter/sets that I use is last N weeks (In this case i took last 4 bi-weekly) and excluding today's week (So week 31 is not included).

       

      So instead of the combination of (Wk24&25 - Wk26&27 - Wk28&29 - Wk30&30), I want the combination of (Wk23&24 - Wk25&26 - Wk27&28 - Wk29&30).

       

      Thanks in advance

        • 1. Re: Flexible Bi-weekly formula
          Naveen B

          Hi Melody,

           

          You are using floor its causing the issue

           

          In the place of FLOOR use round(datepart('week',order date)/2,0)  it will work correctly

           

           

          Hope this helps Kindly mark this answer as correct and helpful so that it will help  others

           

          BR,

          NB

          • 2. Re: Flexible Bi-weekly formula
            Melody H

            Thanks Naveen!

            • 3. Re: Flexible Bi-weekly formula
              Naveen B

              Glad it helped Melody :-)

              • 4. Re: Flexible Bi-weekly formula
                Melody H

                Hi Naveen,

                 

                I still have a question regarding this. I changed to ROUND, but this week it shows double week 31. Do you know why? it fixed after i changed it again to FLOOR...

                 

                 

                Thanks

                • 5. Re: Flexible Bi-weekly formula
                  Naveen B

                  Hi Melody,

                   

                  Could you please attach the sample workbook to check overall data once

                   

                  BR,

                  NB

                  • 6. Re: Flexible Bi-weekly formula
                    Melody H

                    Hi,

                     

                    Please find attached the tableau report (and the data source just in case if you can't open the tableau without it).

                     

                    Regards,

                    M

                    • 7. Re: Flexible Bi-weekly formula
                      Naveen B

                      Hi Melody,

                       

                      It seems for Odd and even weeks  need flip the formula with Round and float

                       

                       

                      Formula to USE:

                      IF (DATEPART('week',DATETRUNC('week', TODAY(),'Monday')))%2=0 THEN

                      'WK '+

                      STR({ FIXED FLOOR( DATEPART('week',[London Date],'Monday')/2):MIN(DATEPART('week',[London Date],'Monday'))}) +

                      " & " +

                      STR({ FIXED FLOOR( DATEPART('week',[London Date],'Monday')/2):MAX(DATEPART('week',[London Date],'Monday'))})

                      ELSE

                      'WK '+

                      STR({ FIXED ROUND( DATEPART('week',[London Date],'Monday')/2):MIN(DATEPART('week',[London Date],'Monday'))}) +

                      " & " +

                      STR({ FIXED ROUND( DATEPART('week',[London Date],'Monday')/2):MAX(DATEPART('week',[London Date],'Monday'))})

                      END

                       

                       

                      Hope this helps

                       

                      BR,

                      NB

                      • 8. Re: Flexible Bi-weekly formula
                        Melody H

                        Hi,

                         

                        Thank you for always helping me solving Tableau!

                         

                        I have an extended question related to this formula. I wanted to make it per 4 weekly and i tried to modify the formula like this but the grouping doesn't seem to be ok.

                         

                        IF [Date]<DATETRUNC('week', TODAY(),'monday') and(DATEPART('week',DATETRUNC('week', TODAY(),'Monday')))%4=0 THEN

                         

                        'WK '+

                         

                        STR({ FIXED FLOOR( DATEPART('week',[Date],'Monday')/4):MIN(DATEPART('week',[Date],'Monday'))}) +

                         

                        " & " +

                         

                        STR({ FIXED FLOOR( DATEPART('week',[Date],'Monday')/4):MAX(DATEPART('week',[Date],'Monday'))})

                         

                        ELSEIF [Date]<DATETRUNC('week', TODAY(),'monday') THEN

                         

                        'WK '+

                         

                        STR({ FIXED ROUND( DATEPART('week',[Date],'Monday')/4):MIN(DATEPART('week',[Date],'Monday'))}) +

                         

                        " & " +

                         

                        STR({ FIXED ROUND( DATEPART('week',[Date],'Monday')/4):MAX(DATEPART('week',[Date],'Monday'))})

                         

                        END

                         

                         

                         

                        Now is week 43 and indeed week 43 (22 - 28 oct) is excluded (based on the formula) but I want the grouping to be per 4 weeks instead of week 42 making its own category (2018 - WK 42 & Wk 45). Do you know how to solve this? Thanks in advance