All Time Flags in Tableau

Hey guys, welcome back again to my blog. Today I will be talking about the problem which many of us have faced during our initial days of learning and implementing Tableau - Time flags!

I cannot even remember how many times I have got people around me asking for this and I had to send them plethora of codes to calculate that.

Now over time, I have improved the calculations (a bit) and I thought it would be useful if everyone had easy access to it.

So first of all, we will need to create the financial year. For that, we need the following two things:

a. Financial Date (Referred to as Date FY in the article)

b. Latest Date till we want to see the values

Date FY

DATEADD( 'month', [Offset], [Date] )

Now, the value of offset depends upon the month from which the financial year starts. It is usually calculated by 1 - Calendar_Month

Eg for April, the value will be 1-4 = -3.

So the calculation will be :

DATEADD( 'month', -3 , [Date] )

In case you want to calculate the FY using some other month, use the below table to find the offset value corresponding to that month.

Now, the first thing to do after creating the financial year is calculate the latest date till which want all of those time flags to be calculated. Let us call this as Latest Date.Now, the first thing to do after creating the financial year is calculate the latest date till which you want all of those time flags to be calculated. Let us call this as Latest Date.

Now the value for Latest Date can be either:

1. Fed through a date parameter, or

2. Use the latest date from the data.

As an example I am using the second option. I am going to use the latest date from the data. The calculation for the same will be:

Latest Date

{ MAX(Date) }

Now as you can see, I have used and Level Of Detail (LOD) expression to find the latest date in the data.

Now since we have the Financial Year and the latest date, we are good to go! But wait, since we will need to compare financial years, we need to convert Latest Date into Financial Year as well. For that, we will do the same thing.

Latest Date FY

DATEADD( 'month', [Offset], [Latest Date] )

Now we can proceed with the flag calculations.

Year Flag

CASE

DATEDIFF( 'year', [Date FY], [Latest Date FY] )

WHEN 0 THEN 'CY' //Current Financial Year

WHEN 1 THEN 'LY' //Previous/Last Financial Year

ELSE ''

END

Quarter Flag

CASE DATEDIFF( 'quarter', [Date FY], [Latest Date FY] )

WHEN 0 THEN 'CQ' //Current Quarter

WHEN 1 THEN 'LQ' //Last/Previous Quarter

WHEN 4 THEN 'LYSQ' //Last year same quarter

ELSE ''

END

Month Flag

CASE DATEDIFF( 'month', [Date], [Latest Date] )

WHEN 0 THEN 'CM' //Current Month

WHEN 1 THEN 'LM' //Previous/Last Month

WHEN 2 THEN 'LLM' //Last to Previous Month

WHEN 12 THEN 'LYSM' //Last/Previous Year Same as current Month

WHEN 13 THEN 'LYLM' //Last/Previous Year Last/previous to current month

ELSE ''

END

Now that we have these flags, we can make Till-date flags. Till date flags are the flags which compare the period till current date or selected date instead of the whole period.

Eg: For 13th Jun 2018, Current Month will be Jun 2018 and Last/Previous month will be May 2018 (whole month). But till date flags will include data from 1st to 13th Jun 2018 and compare it with 1st to 13th May 2018.

Now proceeding to till date flags:

YTD Flag

IF [Year Flag] = 'CY' AND [Date] <= [Latest Date]

THEN 'YTD' //Current Year Till Date

ELSEIF [Year Flag] = 'LY' AND [Date] <= DATEADD( 'year', -1, [Latest Date] )

THEN 'LYTD' //Last/Previous Year Till Date

ELSE ''

END

QTD Flag

IF [Quarter Flag] = 'CQ' AND [Date] <= [Latest Date]

THEN 'CQTD' //Current Quarter Till Date

ELSEIF [Quarter Flag] = 'LQ' AND [Date] <= DATEADD( 'quarter', -1, [Latest Date] )

THEN 'LQTD' //Last/Previous Quarter Till Date

ELSEIF [Quarter Flag] = 'LYSQ' AND [Date] <= DATEADD( 'quarter', -4, [Latest Date] )

THEN 'LYSQTD' //Last/Previous Year Same Quarter Till Date

ELSE ''

END

MTD Flag

IF [Month Flag] = 'CM' AND [Date] <= [Latest Date]

THEN 'MTD'

ELSEIF [Month Flag] = 'LM' AND [Date] <= DATEADD( 'month', -1, [Latest Date] )

THEN 'LMTD'

ELSEIF [Month Flag] = 'LYSM' AND [Date] <= DATEADD( 'month', -12, [Latest Date] )

THEN 'LYSMTD'

ELSE ''

END

Day Flag

CASE DATEDIFF( 'day', [Date], [Latest Date] )

WHEN 0 THEN 'Today'

WHEN 1 THEN 'Yesterday'

ELSE ''

END

BONUS

Still here? Cool! I have decided to tell you one more thing.

Who doesn't like surprises, right? :)

Here you go:

Last N Months

IF

DATEDIFF( 'month', [Date], [Latest Date] ) >=0

AND

DATEDIFF( 'month', [Date], [Latest Date] ) <= N-1

THEN TRUE ELSE FALSE END

That's all folks!

See you around next time :D

Adios!