5 Replies Latest reply on Jun 12, 2019 9:12 PM by Zhouyi Zhang

    Excluding current financial year data from an average calculation

    Andrew Turner

      Hello All

       

      I have a problem where I have a report looking a four years worth of financial year and year-to-date comparison data in a clustered bar chart. SO I need an average line for both the Year-to-date (YTD) and Financial Year (FY) data and the YTD is fine, but there is an obvious problem when averaging FY data because the current FY (starting in April) will skew the average terribly. Any ideas how I can limit the average to only apply to previous FY data?  I am using the following calculation for my average: {EXCLUDE [ReportYear] : AVG({FIXED [ReportYear] : iif(SUM([FinYr])=0,null,SUM([FinYr]))})}. My data looks like this:

      Tableau Table.jpg

      And my graph looks like this - as you can see the average for FY should only be for completed Financial Years:

       

      Tableau viz.jpg