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

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

    Tanner LaFond

      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.

      Tableau Help - Variance.jpg

      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.

      Tableau Help - Growth Rate.jpg

      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
          Patrick A Van Der Hyde

          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