How do you calculate the below logic
For example if the user select "Last Month" Then the report filter startdate is 08/04/2013 and Enddate is 08/31/2013
if the user select "Last Quarter" then the report StartDate is 04/07/2013 and End Date is 06/29/2013
We should always Start With Sunday and End With Saturday.
I looked at this earlier today and read your post carefully. I just didn't get the
For example if the user select "Last Month" Then the report filter startdate is 08/04/2013
I was thinking "What Last Month starts on the 4th of the month?" Now I finally see that you are trying to force the StartDate to be the first Sunday of a month, no matter what day of the month that falls on.
I'm way to tired to tackle this tonight, but I'm sure there's a way to do this. Maybe some of those folks on the other side of the world will find this interesting (I'm thinking of you Dimitri Blyumin ).
Do the start and end of this 'month' have to be contained within the month? For example, if a month ends on Friday, 31-Aug, is the last day Sat 1-Sep, or Sat 25-Aug? Same for the start date.
Yes you are right, We need the Start and end date should be within that period Because when the user run the repot on day 1 then we won't find the saturday for the last week since that Saturday end on 09/05/13.
The below statement works in Sql server
select dateadd(dd,-1,DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,7-datepart(day,dateadd(month,-1,getdate())),dateadd(month,-1,getdate()))), 0))
the same one with small changes for tableau is not working
dateadd('day',-1,DATEADD('week', DATEDIFF('week',0,dateadd('day',7-datepart('day',dateadd('month',-1,now())),dateadd('month',-1,now()))), 0))
am not sure how to achieve this. in tableau. could someone help me to resolve this issue?
To make it work in Tableau, you should probably use this corrected version:
dateadd('day',-1,DATEADD('week', DATEDIFF('week',DATE(0),dateadd('day',7-datepart('day',dateadd('month',-1,now())),dateadd('month',-1,now()))), DATE(0)))
Tableau generally doesn't cast data types on the fly, so you need to explicitly do it, i.e. DATE(0) instead of just 0
This simpler version also works for the first Sunday of previous month, only because Tableau's week starts on Sunday: