1 Reply Latest reply on Jun 6, 2016 12:32 PM by Patrick Van Der Hyde

Need help creating calculated fields - newbie at these, I'm guessing they aren't actually that hard

Hello all,

I need help creating some calculated fields in Tableau.

I've created a simplified version of what I'm working with in Excel, and made a Tableau workbook with it.

Basically, I have two things I need to do:

1. Compare revenue actuals across quarters in Fiscal Year 2016 to the revenue plan in Fiscal Year 2016.  This should be as simple as, for any given quarter: Variance = Revenue Actuals - Plan.  I just don't know how to create a calculated field for this.  I have examples of how it should work in the Excel file.  I've also attached a picture of how it should be in Tableau. 2. Calculate growth rates across quarters of Fiscal Year 2016 to Fiscal Year 2015.  This would just be looking at the revenue actuals, not the plan figures.  I need to calculate the growth from Q1 of FY15 to Q1 of FY16, Q2 of FY15 to Q2 of FY16, etc.  Formula should be Growth Rate = (Amount for Qn of FY16 - Amount for Qn of FY15) / (Amount for Qn of FY15).  Again, just don't know how to create a calculated field for this.  I have examples of how it should work in the Excel file.  I've also attached a picture of how it should be in Tableau. In my actual data set, I have significantly more data and dimensions (things like region, sub segment, etc.).  These formulas would need to be able to filter by those, the same way as my revenue does.  I assume that's not an issue to do that, I would guess it works like that without any extra effort.

If there's anything I neglected to mention, please let me know.  I've attached the Excel file, the Tableau workbook, and some pictures of what I want to see in Tableau.

Thank you in advance for the help - I'm quite lost on this, and want to learn more so I can improve my Tableau skills.

Tanner

• 1. Re: Need help creating calculated fields - newbie at these, I'm guessing they aren't actually that hard

Hello Tanner LaFond,

Attached is a Tableau 9.3 (sorry - I couldn't quickly determine the version you are working with but an upgrade to 9.3 should be free) version of the .twbx showing both of these questions/answers.

The first one is easy to accomplish by utilizing a few calculated fields.

Plan: If [Scenario]="Plan" then [Amount] End

Revenue Actuals: If [Scenario]="Revenue Actuals" then [Amount] End

Variance: sum([Plan])-sum([Revenue Actuals])

Then utilize Measure Values and Measure Names in the view rather than the individual field names.  See the first sheet for the example of this.

Second one - the difference by quarter over year.

The first thing to do is to create the desired output in a crosstab view.  It's just easier to work with for Table Calculations.

Create a quick table calculation from Sum(Amount) - with "Percent Difference" -  This is an option available from the drop down menu of the field Sum(Amount).

Next up.  The calculation will need to be edited in the Table Calculation editor - select "Edit Table Calculation"  - then select "Advanced" for the Calculate the difference along:

Move Year of Date and  Quarter of Date to "Addressing"  and defined 'At the [year of date]" - since we are using both fields to figure out the "Previous" but the Level that defines Previous is "Year of Date".

This will provide a view with the correct percent differences.

For the line chart on Sheet 4, duplicate the chart you just created.  Remove the Sum(Amount) field that is not a Table Calculation.

Hold the CTRL key down and drag Sum(Amount) - the table calc one - to the Rows shelf so it is now on both the Rows shelf and the Label.

Change the chart type to a line.

I hope this helps.

Patrick