4 Replies Latest reply on Dec 21, 2016 11:44 AM by Eli Blankers

    How to define two different fiscal years in single data source

    Sravanthi V

      Hi All,

       

      I have a single data source containing multiple countries financial data. I am showing gross recovery, total expense, and YTD gross recovery etc in dashboard. However, the fiscal years are different for different countries. For example, in case of  NA the fiscal year starts from February while  the fiscal year in Brazil starts from January. I have to give total YTD (summation of all countries) and compare this with last year YTD. So while calculating total YTD I don't know how to set two different fiscal year starts on single date field. Any help would be appreciated.  Thank you in advance.

       

      Merry Christmas and Happy Holidays to all.

        • 1. Re: How to define two different fiscal years in single data source
          Joe Oppelt

          Tableau doesn't have provisions for that.

           

          You'll have to simulate it with your own date calc fields.

           

          But how do you expect to show an overall FYTD total when some countries start on January and some start on February?  What does FYTD even mean in January when some countries have only one month on the books, and others have 12?

           

          If that example means you'll add only January data for a January FY start, but add fully 12 months for a country with a February FY, you CAN make that happen.  The WINDOW_SUM function lets you add  from the current mark to some prior mark, and you can specify that offset with a calc, and the calc can be conditionally set based on the country in question.

           

          I'm tossing these ideas out only at the conceptual level.  I would need an actual sample workbook to play with it and try things out.

          • 2. Re: How to define two different fiscal years in single data source
            Sagar Sanjay Joshi

            There is no straight forward way to do this. you can duplicate the field and set one of the dates FY start as Jan other as Feb and create 2 sheets one with countries with date1 other with Date2 only with the countries corresponding to the certain FY start

            • 3. Re: How to define two different fiscal years in single data source
              Jian Wang

              Is data only aggregated and compared at month level? If so...when prepare dataset, can you add a calculated field for Fiscal Date? Data should look like this. Then you can use FiscalDate rather than the real date of transaction. Hope that helps.

                 

              CountryOrder DateFiscalDate
              Brazil2016010120160101
              Brazil2016013020160101
              NA2016020220160101
              NA2016033020160201
              • 4. Re: How to define two different fiscal years in single data source
                Eli Blankers

                Hi Sravanthi,

                 

                It might make sense to create a hierarchy of calculated dimensions for Year, Quarter, Month, and Day.

                 

                1.  Duplicate your Date Field

                2.  Change your FYs for each separate Date Field

                3.  Create Year Calc:

                     IF [Country] = "Brazil" THEN YEAR(Date1) ELSE YEAR(Date2) END

                4-6.  Create Quarter, Month, Day calcs (All similar, other than Quarter calc requires DATEPART('quarter',datefield)  )

                7.  Create hierarchy, and use those fields.

                 

                Not completely sure this solves the problem, but It would let you compare "month 5" from different FY's pretty easily.

                 

                Thanks,

                Eli Blankers