Multiple (Time Period) Date Calculations

This article examines all things date-related in Tableau, SQL and shares best practices for optimal processes.

In our day to day analytical work, We deal with multiple date calculation requirements, Even though we have already worked with some date logics, we might get stuck with re-framing the same or new date logics. But having all date related logics in one place makes our project or work very easier by less time consuming.

* Time Period Flag.

> Current Month > Rolling Six Months > YTD > Year > Quarter

Step 1 : Create parameter Time Period. It should hold the time period values like Current Month, YTD, QTD, Rolling Six Months etc.

Step 2 : Create one more parameter for the users to filter the month from Report Date parameter based on the selection of month from this Report Date the selected Time Period values will populate.

Example : If we select Nov - 2019 from the Report Date and Rolling six Months from Time Period. The values of Rolling six Months will be from the selected month.

262,434 Is from Nov 2019 - Jun 2019.

Formula To Calculate the Time Period

IF [Time Period] = 'Year' THEN

IF DATEDIFF('year',[Order Date],[Report Date]) = 0 THEN 1

ELSEIF DATEDIFF('year',[Order Date],[Report Date]) = 1

THEN -1 ELSE 0 END

ELSEIF [Time Period] = 'Quarter' THEN

IF DATEDIFF('quarter',[Order Date],[Report Date]) = 0 THEN 1

ELSEIF DATEDIFF('quarter',[Order Date],[Report Date]) = 1

THEN -1 ELSE 0 END

ELSEIF [Time Period] = 'Month' THEN

IF DATEDIFF('month',[Order Date],[Report Date])=0 THEN 1

ELSEIF DATEDIFF('month',[Order Date],[Report Date]) = 1

THEN -1 ELSE 0 END

ELSEIF [Time Period] = 'Rolling Six Months' then

IF

DATEDIFF('month',[Order Date],[Report Date]) >=0 and

DATEDIFF('month',[Order Date],[Report Date]) <= 5 then 1

ELSEIF DATEDIFF('month',[Order Date],[Report Date]) >=6 and

DATEDIFF('month',[Order Date],[Report Date]) <= 11 then -1

else 0 END

ELSEIF [Time Period] = 'Current Month' then

IF DATEDIFF('month',[Order Date],[Report Date]) =0 then 1

ELSEIF DATEDIFF('month',[Order Date],[Report Date]) =12 then -1 else 0 end

ELSEIF [Time Period] = 'YTD' then

IF DATEDIFF('year',[Order Date],[Report Date]) =0

and month([Order Date]) <= month([Report Date]) then 1

ELSEIF DATEDIFF('year',[Order Date],[Report Date]) =1

and month([Order Date]) <= month([Report Date]) then -1 else 0 end

Else 0

END

Formula Explanation In Detail

I have taken a portion of formula I.E one time period to formula to explain in details with example.

ELSEIF [Time Period] = 'Rolling Six Months' then

IF

DATEDIFF('month',[Order Date],[Report Date]) >=0 and

DATEDIFF('month',[Order Date],[Report Date]) <= 5 then 1

ELSEIF DATEDIFF('month',[Order Date],[Report Date]) >=6 and

DATEDIFF('month',[Order Date],[Report Date]) <= 11 then -1

else 0 END

This formula works as follows : We are fixing the formula based on the Report Date, If the report date is Nov - 2019, It will take the last six months from Nov 2019. I.E >=0 Nov-2019

<=5 Jun2019. We are calling this result as 1.

DATEDIFF('month',[Order Date],[Report Date]) >=0 and

DATEDIFF('month',[Order Date],[Report Date]) <= 5 then 1

The result will be used in formula to take the value as follows.

SUM(CASE [Time Period Flag] WHEN 1 THEN [Sales] ELSE NULL END)

Rolling Six Months values 262,434 from Nov 2019 till Jun 2019.

Then we are comparing prior to this six month sales performance. By using the second half formula from the Rolling Six Months Details.

ELSEIF DATEDIFF('month',[Order Date],[Report Date]) >=6 and

DATEDIFF('month',[Order Date],[Report Date]) <= 11 then -1

SUM(CASE [Time Flag] WHEN -1 THEN [Sales] ELSE NULL END)

Comparing sales from prior six month sales from May 2019 - Dec 2018 I.3 202,724.

* Last 3 Months, Last 6 Months and Last 12 Months : Dynamic filter.

CASE [Relative Date Range]

WHEN "Last 3 Months" then [Last 3 Months]

WHEN "Last 6 Months" then [Last 6 Months]

WHEN "Last 12 Months" then [Last 12 Months]

END

Relative Date Range is a parameter : It has the string values. As mentioned in the When condition.

"Last 3 Months is a set and the formula has been written as follows".

DATETRUNC('month',[order_date]) >=

DATEADD('month',-3,datetrunc('month',TODAY()))

and DATETRUNC('month',[order_date]) <=

DATEADD('month',-1,datetrunc('month',TODAY()))

"Last 6 Months is a set and the formula has been written as follows".

DATETRUNC('month',[Order_date]) >=

DATEADD('month',-6,datetrunc('month',TODAY()))

"Last 12 Months is a set and the formula has been written as follows".

DATETRUNC('month',DATE([Date])) >=

DATEADD('month',-12,

datetrunc('month',TODAY()))

Combining Dates to (Month+Year) or (Day-Month-Year)

(CASE [Relative Date Range]

WHEN "Last 7 Days" then STR(DAY([Order_date])) +'-'+

LEFT(DATENAME('month',[Order_date]),3) +'-'+

RIGHT(STR(YEAR([Order_date])),2)

WHEN "Last 3 Months" then LEFT(DATENAME('month',[Order_date]),3) +' '+

STR(YEAR([Date]))

WHEN "Last 6 Months" then LEFT(DATENAME('month',[Order_date]),3) +' '+

STR(YEAR([Date]))

WHEN "Last 12 Months" then LEFT(DATENAME('month',[Order_date]),3) +' '+

STR(YEAR([Order_date]))

END)

In Next Article, I will add more date related calculations.