
1. Re: Calculated Field to show measure for given dimension
Joe Oppelt Nov 1, 2018 8:10 AM (in response to Jordan Brogden)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 Nov 6, 2018 12:22 AM (in response to Joe Oppelt)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

Table Calc.twbx 1.1 MB


3. Re: Calculated Field to show measure for given dimension
Joe Oppelt Nov 6, 2018 7:55 AM (in response to Jordan Brogden)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, reshape 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.)

Table Calc_v10.5.twbx 1.1 MB
