FAQ Series - Fiscal Date

My blog has moved to jimdehner.com 

Thank you for all the support over the past 2 years - I recently updated my blog to make it easier to read, improve the rendering of images and tie back to video presentations that are associated with the content - Check it out at  Jim's new blog  -   

Many businesses use a fiscal calendar for accounting and reporting . Their fiscal year can start on the first day of any month and may not align with the start of a calendar quarter.  Tableau makes it easy to adjust for the fiscal year start.  

Open the date field and set the Default Property - Fiscal Year Start to the proper Month

The fiscal year starts in the month selected (August in this example) and ends 11 months later (July) in the subsequent calendar year 

Quarters are 3 month periods starting with the fiscal year start

The date field can be used in any type of chart, can be filtered, or placed on tile cards to adjust to the proper fiscal year

But problems arise when doing any of calculation based on date functions 

In the LOD below the Year(Order Date) is the calendar year - not the anticipated fiscal year 

{ FIXED Year([Order Date]):sum([Sales])}

Date functions don't recognize fiscal years so a different approach is needed 

This example came from a recent TCish BrainDate- The user has an August fiscal year start and wanted the to be able to select any calendar date and display the current and the prior  YTD and QTD sales 

(Note: in the US retail sector February and August fiscal year starts are not uncommon - Inventory taxes are often evaluated on inventory levels at the end of January  so retailers set the FY start in February or 6 months later - manufacturers who sell into the segment frequently want to be able to see reports or analyses in the retailer Fiscal Year)

Conceptually, the approach is simply determining the fiscal year start date based on a parameter and the Current Date (a calendar date) set by another parameter - used as the end date for YTD and QTD analysis.  

Note: The formulas to determine the quarterly adjustment between the fiscal quarters and the calendar month were difficult and took some trial and error - for that reason I included them in the text so you could copy and paste them - the other calculations deciding which calendar dates are between the start and end dates of the fiscal period. 

Start by creating a parameter to select a start month - The values are 1-12 and the display is the Month to make it easy on the user

another parameter is simply a date parameter 

The FY start date is just based on the Start date parameter converted to a real date

  Dateadd('year',0,[Convert Start date to Real date])

But finding the month of the quarter start date will take a series of calculations  

The basis to determine which Calendar Year the contains the Fiscal Quarter start -  if the value is positive the Current Date and the FQ start are in the same calendar year - if the value is negative the FQ starts in the prior calendar year

month([Current Date]) - Min(Int([FY Start Month Parameter]))

The adjustment that will be used in Dateadd function is actually

If [Number months Current date and FY Start]>= 0

then [Number months Current date and FY Start] 

ELSE 12+[Number months Current date and FY Start] end

The FQ for the Current date is 

If  [Month adj to FQ1] <=2 then Dateadd('month',0,[Start Date - Real])

ELSEIF   [Month adj to FQ1] <=5 then Dateadd('month',3,[Start Date - Real])

ELSEIF   [Month adj to FQ1] <=8 then Dateadd('month',6,[Start Date - Real])

ELSEIF   [Month adj to FQ1] <=12 then Dateadd('month',9,[Start Date - Real])END

For the prior year fiscal year start the formula simply is:

Dateadd('year',-1,[Start Date - Real])

The prior quarter is 

If  [Month adj to FQ1] <=2 then Dateadd('month',-3,[Start Date - Real])

ELSEIF   [Month adj to FQ1] <=5 then Dateadd('month',0,[Start Date - Real])

ELSEIF   [Month adj to FQ1] <=8 then Dateadd('month',3,[Start Date - Real])

ELSEIF   [Month adj to FQ1] <=12 then Dateadd('month',6,[Start Date - Real])

END

The formulas of the YTD QTD prior YTD and Prior QTD are straight forward LODs that total the sales between the period start and end dates

YTD sales for the Current Fiscal year

{ FIXED [Order Date]:(if min([Order Date])>=[Current FY start date] 

and min([Order Date])<=[Current Date] then sum([Sales]) end)}

QTD sales in the current FY

{ FIXED [Order Date]:

If Min([Order Date])>=[Current Q start date] and Min([Order Date])

<=[Current Date] then sum([Sales]) end }

Prior fiscal year YTD sales

{ FIXED [Order Date]:(if min([Order Date])>=[Prior FY start date] 

and min([Order Date])<=DATEADD('year',-1,[Current Date]) 

then sum([Sales]) end )}

Prior year fiscal quarter sales 

{ FIXED [Order Date]:if Min([Order Date])>=[Prior Q start date] 

and Min([Order Date])<=DATEADD('month',-3,[Current Date]) then

sum([Sales]) end }

and the YoY and QoQ formulas are LOD's 

sum({ FIXED :(sum([Current Year YTD sales])-sum([Prior Year YTD sales ]))})/

sum({ FIXED :  sum([Prior Year YTD sales ])})

and 

sum({ FIXED :(sum([Current Year QTD sales])-sum([ Prior QTD sales]))  })/

sum({ FIXED :sum([ Prior QTD sales])  })

In table form the user can select the starting dates and the table updates to return the year and quarter to date values 

A look at the monthly detail makes it easier to see what the formula do 

The current YTD include the months of August - December 5th, QTD is November  - December 5th

The prior YTD is in FY 17 August 2017 - December  5, 2017 and the prior QTD is in FY 18 August 2018 - September5, 2018

Hope it makes sense to you

The workbook which contains all the complex formulas can be downloaded at Link to Tableau Public

Enjoy

Jim

FAQ Series - Business Days

My blog has moved to jimdehner.com 

Thank you for all the support over the past 2 years - I recently updated my blog to make it easier to read, improve the rendering of images and tie back to video presentations that are associated with the content - Check it out at  Jim's new blog  -   

Often you only want to use "Business Day" in your viz or move the weekend sales to Friday or Monday -

In the examples here I will us the US standard Sunday - Saturday week with a Monday - Friday "Business Week"  - 

1-Weekday Labels

Tableau has a couple ways to identify and label dates by the day of the week - 

A straightforward way is take advantage of the Custom Date option

Open the Date dimension - then Custom Date - then Weekdays from the dropdown

Alternately - you can use Datename in a calculated filed - the argument 'weekday' must be the literal as shown in quotes

DATENAME('weekday',[Order Date])

Or use Datepart to assign a number to the weekday - 1 for the first day of the week (Sunday in my examples) and 7 for the last day (Saturday)

DATEPART('weekday',[Order Date]) 

 

 

Sales by day of the week is  easy to get - but how to you move the weekend sales to Friday - You can use an "IF ... Then " type of conditional or a Case statement like this

CASE [Order Date (Weekdays)]

When 2 then "Monday"

When 3 then "Tuesday"

When 4 then "Wednesday"

When 5 then "Thursday"

Else "Friday-Sunday" End

2- Examples

The day of the week dimension can be used like any other dimension to categorize data - 

or in any type of calculation

Some users want to look at how their average sales vary by the day of the week

 - Or like this YoY by day of the week - (see  https://jimdehner2.blogspot.com/2019/12/yoy-three-different-approaches.html )

3-Holidays

Holidays, including those that are unique to region or business culture, often need to be removed from the dataset - 

One way is to create a separate dataset of the date and the holiday name:

Left join the holiday file with your data set on the date field

and then use a Data Source filter to delete all the holidays from the data

 

4-Look back N business days

Some users want to find a value in the several days prior to the current day - It can be done easily with a datediff function when all 7 dates are included.  But to look back N days excluding weekends is more complex.  

The example below is broken into individual formulas that you can combine in your own workbook - also it determines the number of calendar days to look back based on the business need to express the look back in business days - the solution uses actual date values so dimensional filters can be applied without affecting the calculation 

First the Lookback period in business days is set using a parameter (just a simple integer)

To simplify some of the I wanted to wanted to use Monday as the first day of the week and used the previous Datepart week formula in:

if [date part weekday]>=1 and [date part weekday]<=7 then

[date part weekday]-1 else 0 end

You may be tempted to use datepart('weekday',[date],'monday')  - unfortunately datepart only supports weekday for weeks that begin on the default - in my case Sunday

Next we need the number of 5 day weeks back based on the value of the Business Day Back Parameter - the Min Number of Weeks is:

Int(([Number of day back]+1)/5)

but that is the number of whole weeks and it needs be adjusted for the position of the date within the week - for that use a function called modulo - the function returns the remainder of the division of the argument (the parameter value +1) divided by  5  

([Number of day back]+1)%5

The total number of weekend then is

 

{ FIXED [Order Date]:(

if min([jd datepart start monday])=0 then Min([JD Number of weeks ] )

elseif min([jd datepart start monday])<int(Min([jd modulo day of week]))

then Min([JD Number of weeks ])+1 else Min([JD Number of weeks ] )end ) }

The formula used to add in the Saturday/Sunday weekend days to the Business Days Back parameter to determine the number of calendar days to use in the date diff calculation

int(min([Number of day back]))  + 2*(min([number of weekend days]))

finally the calendar date for N Business days in the past can be determined as

{ FIXED [Order Date] :DATE(dateadd('day',-[JD number of dates back],Min([Order Date])))   }

For example using December 2019 Superstore data looking back 7 business days would return :

Great but real business questions are usually about the growth from the over the period or the total sales looking back over all the dates.  To get that we need a start date and a parameter. First determine and fix the calendar date based on the start date and the number of business days to look back

{ FIXED : min(if [Enter start date]=[Order Date] then [JD date of back dated] end )}

then the value on that date becomes:

IFNULL({ FIXED :sum(if [JD Fixed back date from param]= 

[Order Date] then [Sales] end)}  ,0)

 

The ifnull adjust for dates where there were no sales

The value on Start Date is simply

IFNULL({ FIXED [Order Date]:   sum( if DATETRUNC('day',[Order Date]) = 

DATETRUNC('day',[Enter start date])  then    [Sales] end )} ,0)

And the percent difference is:

If (sum([JD value at lookback date]))=0 then 1 

elseif (sum([jd value on start date parameter value]))=0 then 0

else(sum([jd value on start date parameter value])-  sum([JD value at lookback date]))/sum([JD value at lookback date])

end

All the calculations are LOD's and can be used to create a simple summary table 

Determining the the total sales over the "lookback period" will require another LOD

IFNULL({ FIXED :sum(

if [JD Fixed back date from param]<= [Order Date] and 

[Order Date]<=[Enter start date]

then [Sales] end)}  ,0)

Lots of LOD's and can be confusing but necessary to avoid table calculations which are based on a position in a table and not an actual date.  

There could be many more examples and variations on viewing sales by business day. I encourage you to try some of your own - 

The examples here are captured in a workbook that has been posted to my public site at 

https://public.tableau.com/profile/jim.dehner#!/vizhome/Salesbyweekday/Dashboard1 

Jim

 

FAQ Series - Duration and Time - Not the same thing

Recently there have been a number of Forum questions on converting time to duration 

Time and duration are not the same - Time can be read from a clock - duration is the accumulation on intervals - and although we use a common terminology  - hours - minutes and seconds they are different 

This example uses a simple data set consisting of project start and end times by employee

Date Functions - (see https://jimdehner2.blogspot.com/2020/02/dates-functions-and-other-necessary.html)

The date functions in Tableau are based on a truncating the date/time structure shown below 

If you needed to determine the number of days between the start and end dates you could use                       

 Datediff('day',[Start date],[End date]) 

 and  the value for project A9 would be -  27

but the actual interval is 27 days 1 hour 1 minute  and 32 minutes 

Increasing the number of decimal places will not change the result - Datediff truncated the value at the 'day' level

So how can we get Tableau to return the actual duration - 

Determining the duration

The solution is to build the duration from the lowest level (grain) in the data - the example here is in seconds - Yours may differ and you can adjust the model as needed - also we will want to be able to calculate the duration at different levels in the data - this LOD will meet that need

{ INCLUDE [Employee],[Project]:sum( DATEDIFF('second',[Start Date],[End Date])  )}

The LOD will return the difference in seconds - 

The duration are large and not what you want. They need to be converted to hours, minutes, and seconds 

The Duration in Seconds is 

sum({ INCLUDE  [Employee],[Project]:sum([datediff at the second LOD]%60)})%60

Again using an LOD to allow summing at different levels in the data -  Note the use of MODULO     -  the function returns the remainder of the measure (here the total seconds)  divided by the value that follows the % sign (60)   - the LOD will first sum the remaining seconds at the level of the viz - the second use of modulo converts the total to minutes and seconds during the accumulation across the employee level and above.

The Duration in Minutes is 

int(sum({ INCLUDE [Employee],[Project]:sum([datediff at the second LOD])/60 }) %60)

The Int() or Floor() will returns the integer portion of the calculation rounded down 

The Duration in Days is

int(sum({ INCLUDE [Employee],[Project]:sum([datediff at the second LOD])  }) / (60*60*24))

In text table form the viz is

Or in summary 

Format dd:hh:mm:ss

Often users have asked to have a format similar to dd:hh:mm:ss

str([duration days])+":" +str([duration hours])+":" +str([duration minutes])+":" +str([duration - seconds]  )

The formula leverages the integer value in  the LOD (integer) values and converting them to strings 

and returns this in summary 

Time and duration are not the same and it does take a few steps to getthe actual duration in a format the user expects - Hope this helps the next time you are faced with a similar problem

The workbook used in these examples can be found at      

https://public.tableau.com/profile/jim.dehner#!/vizhome/Durationintimeformat/Dashboard1

Enjoy

Jim