3 Replies Latest reply on Oct 11, 2017 10:10 PM by Deanna Bayliss

    5 year average, current values for financial years

    Deanna Bayliss

      Hi Tableau Team,

       

      I have a fairly simple request that I just cant work out.

       

      I have data that extends back for 10 years or so. On a dual axis graph I would like to show:

       

      -The sum of the current financial years values for each month as a bar graph (i.e. FY 18 commences July)

      -The average for the last 5 years (financial years commencing July) values corresponding to each month as a line graph (i.e. FY 13, 14, 15, 16, 17)

       

      It seems quite simple, the problem I am facing is:

      - filtering data to the last 5 years excludes the current FY for the bar graph

      - using calculations such as .....if [Date (Years)] = YEAR(TODAY()) THEN [Number] END.....works but only for the current non-financial year

       

      Any suggestions? The desired graphic is below FYI.

       

      Thank you kindly

      Averag.JPG

        • 1. Re: 5 year average, current values for financial years
          Deanna Bayliss

          I should mention, that the solution should not be hard coded into a formula. So when the financial year changes the values are updated automatically. (i.e. so not using formulas pointing to specific years)

          • 2. Re: 5 year average, current values for financial years
            Jennifer VonHagel

            Hi Deanna, Tableau will get you part of the way there in allowing you to set and display Fiscal Year, but its date functions will not in anyway recognize the Fiscal Year. So the date calcs won't make life easy:

             

            Set a fiscal year:

             

            Now when you set up your view, it will show Fiscal Year starting in July. If we bring Order Date to the Filter shelf, we can filter on the FY values:

             

            But, I went ahead and converted order date back to a regular January-start year, because it is useless to us in formulas anyway.

             

            So I created these formulas to derive Fiscal Years and Months. I didn't use all of them in the viz, but used them to qa my work along the way:

            Fiscal Year = IF MONTH([Order Date]) <= 6 THEN YEAR([Order Date]) ELSE YEAR([Order Date])+1 END

            Fiscal Year Label = 'FY ' + str([Fiscal Year])

            Fiscal Month = IF MONTH([Order Date]) <= 6 THEN MONTH([Order Date])+6 ELSE MONTH([Order Date])-6 END

            Month Name = DATENAME('month',[Order Date])

            After creating month name, I right clicked on it in the Dimension View, chose Default Properties, chose Sort, and sorted it with July first. Now it will automatically show up in charts and tables sorted properly.

            Current Fiscal Year = IF MONTH(TODAY()) <= 6 THEN YEAR(TODAY()) ELSE YEAR(TODAY())+1 END

            Is Current Fiscal Year: [Fiscal Year] = [Current Fiscal Year]

             

            Ok, here's a table checking the new Fiscal Dates against regular Order Dates. Things appear to be in order.

            So let's create calculations based on these dates to get current fy sales, and prior 5 fy years avg:

            Current FY Sales = SUM(IF [Fiscal Year] = [Current Fiscal Year] THEN [Sales] END)

            Previous 5 FY Sales Avg =

            { FIXED [Fiscal Year], [Fiscal Month] :

                SUM(IF [Fiscal Year] >= [Current Fiscal Year]-6 AND

                       [Fiscal Year] <= [Current Fiscal Year]-1

                    THEN [Sales] END)

            }

             

            Then I put it together in a view, and I think it works.

             

            I checked the 5 year average - this sample data doesn't have 5 prior years, just a few, so it's not fully qa'd, but the Grand Total (which is an AVG not a SUM) for all prior years available does match the numbers in the line chart.

            Workbook is attached, hope this helps!

             

            Jennifer

            • 3. Re: 5 year average, current values for financial years
              Deanna Bayliss

              Hi Jennifer,

               

              Thank you so much for the effort you put into this.

               

              Although, with my data it did not quite give me the right answer, it helped me greatly! I have managed to amend it slightly to do exactly what I need it to do!

               

              The logic I was missing was breaking the fiscal year up into measurable fields with which to use in logic calculations.

              In the end, the LOD calculation was unnecessary and seemed to be duplicating values. The average was also being applied to the number rather than the year, so for instance a sum of 6 and a count of 12 averaged 2, when in fact the average for the last 5 years needed to divide the sum by 5, so = to 1.2.

               

              I removed the fixed LOD from the calculated field and divided the result of the if statement by 5 and YAY, success!

               

              Thank you again for your help!

              Deanna