Optimized Combination of "Fiscal Calendar Table" and "Tableau's calendar calculation"

Version 1

    Background:

    I was asked to create seamless monthly trend chart across multiple years with using our fiscal calendar.

    I thought it is easy, but actually I needed to spend some time for trials and errors with some confusion.

    The logic itself is easy, but there are a lot of confusion if you do without clearly understanding the feature of "Fiscal Year".

    Target People:

    People who are using company defined Fiscal calendar its fiscal month can no be calculated with the math.

    Typically, companies which use 5-4-4 (4-4-5, 4-5-4) = "13 weeks quarter" type calendar.

    Digest:

    As long as your Fiscal calendar can be defined only with standard calendar, you can use Tableau's excellent function of "Date" calculation. 

    However if your Fiscal calendar is special and requires special table, you need to use "join" and date calculation becomes tricky.

    It brings some confusion relating to Month sorting and concept of "Fiscal Year" because Tableau's default function is not available in this case..

     

    Even Fiscal Calendar requires joined table, "Optimized Combination" of "Tableau's calendar calculation" and "Fiscal Calendar Table" brings appropriate visualization.

    Problem Statement (One example) :

    With using Special Fiscal Calendar, I cannot draw seamless monthly trend chart (No fiscal year pane) across multiple year.

     

    Goal/Assumption:

     

    Goal Image:

     

    We are using special calendar which respective fiscal month/year does NOT start from 1st day of standard calendar Month.

    Fiscal Year start is "September of special calendar".

     

     

    Data is something like this

     

    We can join the data's date and calendar Date

    table summary looks like below..

     

    Bad Case:

     

    Case- 1

    Use Original Date

     

    Of course it does not match Fiscal Month Summary

     

    Case-2

    Create fiscal Month Field as date format

     

     

     

     

    Number is correct, but sort is off (=Starting from January).

     

    Change Fiscal Date Start Month.

     

    Actually Nothing changed.

     

    Adding "Fiscal Year" to dimension, AND change the format to "Month"

     

    It works.

     

     

    But this is still not seamless chart, and it confuses people because

    "Fiscal Year" and Tableau calculated fiscal year (="Year(Fiscal Date)" is different.

     

     

     

    Case -3 :

    Use original "year" + fiscal month

     

     

    Looks Good ?!?

     

     

    This is an example of bad audit.

     

     

    Remember, the fiscal calendar's start date is not January 1st.

     

    This approach does not work neither.

     

    Solution:

     

    Create "Fiscal Date Mod" with partially adjusting "Fiscal year".

    Because Fiscal year start from September, I only change month of "9,10,11,12" to the date of 12 months back. 

    int ( [fiscal month] /9) ==>

          1~8 = 0

         9~12 =1 Multiplying (-12) makes only "9~12" back by 12 months.

     

    You can change "9" to your fiscal year start month.

     

     

     

    With changing "Fiscal Year start" to "September",Tableau's Fiscal Date calculation" and Year of "Fiscal Year Mod" is exactly same.

     

    Conclusion:

    With

         - Creating "Modified Fiscal Date" (actually it only defines month) by partially changing fiscal year

         - And set Tableau's Fiscal Year Starts

    You can get appropriate "Date" calculations (for example) which can create seamless monthly trend chart.

     

    int ( [fiscal month] /9) ==>

          1~8 = 0

         9~12 =1

    Multiplying (-12) makes only "9~12" back by 12 months.

    You can change "9" to your fiscal year start month.

     

     

    (2017/10/01)

    Shinichiro Murakami