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

# what is equivalent to sql statement ?

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 ?

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 ?

Basically this is calculating the week number

Thanks,

Gourav

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

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