1 Reply Latest reply on May 2, 2017 2:08 PM by Joe Oppelt

    Fiscal year formula help

    tableau gstl

      Hi Need help on the below formula

       

       

      Attr= (TotalExits/YTDHc)*12/Number of months

       

      When I select year = 2015 and month =10

       

      then formula is

       

       

      Sum(Total Exits for (Apr+may+jun+july+aug+sep+oct)/(sum(HC Apr+may+jun+july+aug+sep+oct)/7(which is count number of months from april till oct)) *12/7 count of number of months

       

       

      Can some one please help

       

      Similarly when i select months which are <= 3 then the data from month >=4 from current year till next year march should be added for both exits and HC

       

       

      The calculation is based on Fiscal year starting from April

       

      Thanks in advance

       

      Attached sample workbook for reference

        • 1. Re: Fiscal year formula help
          Joe Oppelt

          (Note to self:  V 9.0 here)

           

          First of all, you're usually better off letting Tableau do date manipulations for you.

           

          To that point, I created a date field from your YEAR and MONTH fields.  MAKEDATE builds a date for you.  (I just started all dates on the first of the month here.)

           

          And Tableau handles fiscal calendars for you.  I did right click on the date field I made, selected DEFAULT PROPERTIES, and from that pull-down, selected FISCAL YEAR START.  And then I selected April.

           

          Now the date4 field knows that the fiscal year starts in April.  See Sheet 2.  The filter treats the years as fiscal years.  I can select a fiscal year (such as 2015) and tableau displays the dates from that fiscal year.

           

          Now go to Sheet 3.

           

          Here I changed your parameter names to fiscal names.  And I edited the MONTH parameter.  Check it out.  It still has the numeric values and is a numeric parameter, but the DISPLAY values are character strings and will have more meaning to the user.  I matched the fiscal month to the month name, so April is 1, May is 2, etc.

           

          And I created two numeric dimensions:  Fiscal month and Fiscal Year.  This takes the month or year from the raw date (calendar value) and converts it to a fiscal value.  On Sheet 3 I put two years worth of data.  Next I listed the original month and year, and also the new fiscal month and year.

           

          For demonstration purposes I displayed [Total Exists].  And then I created a calc that grabs the values within the fiscal year selected, up to the fiscal month selected.  (All other rows will have NULL in them.)  Both these are displayed on the sheet in the text column.

           

          Finally I did a SUM of those special values.  I display that in the title.  Now if you mess with the parameters you will see things change accordingly.

           

          you can take these principles and apply them however you need for your purposes.  If you want to divide by the number of selected months, the Fiscal Month parameter contains that number already.