3 Replies Latest reply on Jan 23, 2014 1:22 PM by Matt Lutton

    Creating a calculated field for a specific year

    Carey Smith

      I am trying to create a calculated field that displays a single year's data for a measure. For example, in the image below, I'd like the "Profit Ratio - 2012" column to only show data for 2012, while the "Profit Ratio" column shows the value for all years. I'm creating calculated fields because I'm displaying the values in a table that contains a sparkline (using the technique described here: Add Sparklines to a Tableau Table ).

       

      I'm running into a problem with [Order Date] wanting to be aggregated when used in a calculation with [Profit Ratio]. I've tried using ATTR to aggregate [Order Date], but when there is data for more than 2012, the calc returns *  and the field is left blank.

       

      Does anyone know of another method for filtering out a specific year's data in a calculated field?

       

      Any help is appreciated!

      Carey

       

      profitRatioByYear.png

        • 1. Re: Creating a calculated field for a specific year
          Matt Lutton

          If you put Profit Ratio on a separate sheet with the same layout, with "Show Headers" unchecked for the dimension headers and a different YEAR(Date) filter on each sheet you can then combine them on a dashboard.  The resulting dashboard is attached and shown below.  This has its limitations, as well, and there's probably a way to get this in one sheet but I knew this was one approach to get at what you wanted:  Hopefully a guru will show us another simpler approach.

          Profit Ratios.png

           

          Note I didn't bother lining up the values, but we could line everything up if we worked at it a bit.

           

          I did this in Version 8.1, not Version 8.0, but the process would be the same.  If you're unfamiliar with that process, and think this would work for your scenario, I can explain it in more detail.  One complication is exporting to Excel will have to be done on a per-sheet basis; there are other implications of using two sheets as well.

           

          8.1 workbook attached.

          1 of 1 people found this helpful
          • 2. Re: Creating a calculated field for a specific year
            Carey Smith

            Thanks for such a quick response Matthew. In the actual workbook, we have more calculated fields in the table with a few dimension filters, and this is all part of a larger dashboard. I'm hoping to avoid piecing together the table out of multiple worksheets, but it's a good idea if there isn't a single sheet solution!

            • 3. Re: Creating a calculated field for a specific year
              Matt Lutton

              No problem.  I probably should be able to figure this out, but am having a hard time wrapping my brain around the issue.  I'll bet Joshua Milligan can help create a one sheet solution