3 Replies Latest reply on Nov 6, 2018 7:55 AM by Joe Oppelt

    Calculated Field to show measure for given dimension

    Jordan Brogden

      Hi,

       

      I need a calculated field to show the given measure for a given time period,

       

      For example I want to be able to show Revenue for 2017 in a calculated field.

       

      This may be fairly easy but I'm struggling a bit and need help!

       

      The reason I need this in the view as a calculated field is I have a series of Quick Calculations calculating % difference to last year, I then want to hide one of the years, but hiding a year hides everything in the view for that year.

       

      So I need 2017 revenue to be fixed in the view even if 2017 as a 'year dimension' is hidden.

       

      I hope that makes sense. So it would be [year]=2017 [revenue] or something along the lines but I don't know how to write it properly in Tableau

       

      Thanks,

      Jordan

        • 1. Re: Calculated Field to show measure for given dimension
          Joe Oppelt

          The reason things disappear when you select one year is that filtering deletes the other year rows from the underlying table, and therefore there isn't a previous year to do a % difference against.

           

          Instead of using a quick filter for year, use a table calc.

           

          Make a calc in this format:

           

          LOOKUP( ATTR([some dimension]), 0 )

           

          Right now you have something on your filter shelf that is letting you pick a year.  If it's a separate [Year] field, just put that in place od [some dimension] above.  But if your filter is something like YEAR([Order Date]), then drag the pill that is currently on your filter shelf into your calc editor.  Tableau will expand the syntax it uses to do that, and that's what you want to put in place of [some dimension] above.

           

          Use that calc as your filter instead of what's currently there.  The LOOKUP is a table calc.  When you use a table calc as a filter, you no longer delete rows in the underlying table.  It leaves the table intact so that other table calcs (such as %diff) can have the data they need to execute properly.

          • 2. Re: Calculated Field to show measure for given dimension
            Jordan Brogden

            Hi Joe,

             

            This is great and very close to what I'm after, my table is a lot better so thank you.

             

            The final thing is I'm left with a blank column for the previous year

             

            I've attached a workbook to show what I mean, maybe you can see what I'm not quite doing right, I'm looking to show Quantity and Sales as the actual values for the 2 years, but for YoY just the 2017 column in this example, and get rid of the unnecessary blank column if that makes sense?

             

             

            Thanks,

            Jordan

            • 3. Re: Calculated Field to show measure for given dimension
              Joe Oppelt

              The shape of your sheet dictates that 2016 will show up for all measures because YEAR is on the sheet.

               

              The method I suggested lets you take 2016 off the sheet, and you'll notice that 2016 data is still available for the %YOY calcs to do their thing.

               

              If you want to display "this year" and "last year" and then show a calc that is a function of those two numbers, re-shape your sheet as I have shown in the second sheet in the attached.  (I did it for quantity.  You'll need to do it for sales.)

              1 of 1 people found this helpful