7 Replies Latest reply on Sep 19, 2013 12:03 AM by Dimitri.B

# Last Month , Last Quarter, Last year calculation based on parameter value

I have a below report. The requirement is

user to select the parameter like below

"Last Month"

"Last Quarter"

"Last Year"

"Current Year"

In the report top, we are showing report Start Date & End Date to the user.

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.

• ###### 2. Re: Last Month , Last Quarter, Last year calculation based on parameter value

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.

• ###### 3. Re: Last Month , Last Quarter, Last year calculation based on parameter value

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 ).

--Shawn

• ###### 4. Re: Last Month , Last Quarter, Last year calculation based on parameter value

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.

• ###### 5. Re: Last Month , Last Quarter, Last year calculation based on parameter value

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.

• ###### 6. Re: Last Month , Last Quarter, Last year calculation based on parameter value

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?

• ###### 7. Re: Re: Last Month , Last Quarter, Last year calculation based on parameter value

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:

DATETRUNC('week',DATETRUNC('month',DATEADD('month',-1,NOW()))+7)