Sometimes, you need to create join between Worksheets or Tables using a column of data that doesn’t exist in your data worksheet. Doing these kinds of joins takes a few extra steps.
One case where you may need to join on a calculated column is joining your monthly goals table onto a table with your revenue data. We’ll walk through this example below.
1. Create your calculated column. This will be the column you’ll use to join your data together. For our example, we’re going to create a new column using DateTrunc so that all the invoices in a month are all associated with a a date containing just the month and year.
2. Create a Reference Worksheet. The next step is to build a new Worksheet the references the data in your current Worksheet, also known as a Reference Worksheet. The quickest way to create a Reference Worksheet is to go to the left side navigation menu and click the ‘…’ next to the name of the Worksheet you would like to build on, and then click ‘Reference’.
This creates a new Worksheet that pulls in all of the data from the base Worksheet. The base Worksheet is treated as though it is a table in a database.
3. Join to the Reference Worksheet. Open up your new Reference Worksheet. You can now join the Monthly Goals table to this new Worksheet. Simply add a create a join like normal, and select the column you created on the first Worksheet as the join key!