2 Replies Latest reply on Oct 28, 2013 9:49 AM by Ramon Martinez

How to create calculated field based on data in different rows

I'm relatively new to Tableau and most of the analyses I want to do involve creating calculated fields that add/subtract data displayed in different rows. So for example how do I create a calculated field that is equal to the employment rate for California minus the employment rate for the US for each date (X/X/XX). Here's what my data looks like:

Date     Place     Employment Rate

1/1/11     CA     .54

1/1/11     US      .59

2/1/11     CA     .55

2/1/11     US     .59

Essentially I want to subtract the employment rate in one row (CA) from that of another row (US) whenever the dates in the rows are the same. I'd like the data to look like this:

Date     Place     Employment Rate     Diff Emp Rate

1/1/11     CA     .54                              .05

1/1/11     US      .59                             .05

2/1/11     CA     .55                              .04

2/1/11     US     .59                              .04

Thanks!

• 1. Re: How to create calculated field based on data in different rows

See the attached.  With the view set up as Date > Place > Sum(Rate) on the rows shelf, duplicating the Sum(rate) and choosing Quick Table Calc > "Difference", then customizing/editing that table calc to create a calculation (named Difference in this example) that will be available in the data window. This table calc is set up as addressing: Place, partitioning on Date.

If you have any questions or need help applying this to your actual workbook, don't hesitate to ask or post your actual workbook along with the desired outcome you'd like to see.

• 2. Re: How to create calculated field based on data in different rows

Hi Alissa,

As Matthew said. He was quicker than me.

This is a variant of the same concept explained by Matthew. Check the attached workbook.

Best,

Ramon