3 Replies Latest reply on Feb 26, 2017 1:43 PM by Shesil Parmar

    what is equivalent to sql statement ?

    Shesil Parmar

      Hi

      I have a SQL statement which i'm converting into tableau report.

       

      There is a line    

      SELECT FLOOR((DAYOFMONTH(NOW())-1)/7)+1) AS 'week',

       

      i dont know how to convert this into tableau calculation.

       

      any ideas ?

       

      thank you.

        • 1. Re: what is equivalent to sql statement ?
          Carl Slifer

          Howdy Shesil,

           

          It looks like it is counting which week for each month that a day applies to. So that each day is in a week 1 -5. Each week is defined as the first 7 days of a month, then the second 7 days etc.

           

          In tableau we have a function called DAY() the DAY() function takes today's day of the month. So we will use DAY([Date]).

          Then because of later maths (dividing 7 by 7 and taking the int of that would result in the value 2)  we will then subtract 1. So that the first of the month shows as 0 and the 7th shows as 6.

          (DAY([Order Date])-1)

          Next we will take the above value and divide it by 7 and take the integer value of this. Such that the initial value of 7 was now 6 and the integer value returns 0

          INT((DAY([Order Date])-1)/7)

          Finally we add one to this to make the first period show as 1.

          INT((DAY([Order Date])-1)/7)+1

           

           

           

          And wa-la!

           

          Best Regards,

          Carl Slifer

          InterWorks

          1 of 1 people found this helpful
          • 2. Re: what is equivalent to sql statement ?
            Gourav Sharma

            Basically this is calculating the week number

             

            Thanks,

            Gourav

            1 of 1 people found this helpful
            • 3. Re: what is equivalent to sql statement ?
              Shesil Parmar

              Thanks Carl, i was able to work it out .. but your insight was really helpful.

               

              how to do work with above statment without any date filed from your data source, (instead of order_date field , take system date), i have got below calculated filed and seems its working

               

              round(DAY(TODAY())/7,0)+1