3 Replies Latest reply on Feb 14, 2019 11:55 AM by Jim Dehner

    Pivot Table advanced-calculation column

    Joseph Delli Gatti

      Hi,

       

      If I knew terminology better, I could probably find this topic somewhere here... .

       

      In my original dataset, I have several columns: I have one column called "LOB", one called "DataType", one called "Year", and one called "Amount" .

       

      LOBDataTypeYearAmount
      LifePremium201525
      LifeClaims20153
      LifeClaims201619
      DentalPremium201616
      DisabilityClaims20184
      DentalReserves201920
      DisabilityReserves201813
      DisabilityClaims20181
      etc....

       

       

      I have pivoted my data so that LOB is the row data, and Year and DataType are the column data.  The amount is summed for each.

       

      20152015201520152015
      LOBClaimsCapitationReservesPremiumCalculation
      Life120220=SUM(Claims, Capitation, Reserves)/Premium
      AD&D3016=SUM(Claims, Capitation, Reserves)/Premium
      Dental8010=SUM(Claims, Capitation, Reserves)/Premium
      Disability70313=SUM(Claims, Capitation, Reserves)/Premium

       

       

      I need to do the following calculation:

       

      within each "Year", I need to SUM('Claims', 'Capitation', and 'Reserves') type data / 'Premium' type data and have it display to the right.

       

      I'm new to Tableau, so please baby-talk me through it.

        • 1. Re: Pivot Table advanced-calculation column
          Jim Dehner

          Hi Joseph

          please see the training videos on connecting to data and Prep Tableau Training: View Training Courses

           

          you want the data in a tall narrow column lik

               LOD     Date     Data Type     Value

           

           

          that structure will make aggregation across multiple date periods much easier

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Pivot Table advanced-calculation column
            Joseph Delli Gatti

            Sorry - what I'm looking for is within a pivot table I've already created, I need to do an equation: (Claims+Capitation+Reserves)/Premium.

             

            In the original dataset I have an amount column that is accompanied with a support column noting if the amount is Premium, Claims, Reserves or Capitation.  I have pivoted this so now I have a pivot header of Claims, Capitation, Reserves and Premium.  I need to add an additional column to the pivot table that is the above calculation.

            • 3. Re: Pivot Table advanced-calculation column
              Jim Dehner

              Yes I understand that - The data structure is not taking advantage of the database capabilities of tableau-

              so you will need a separate calculation that takes the form of (sum(claims)+sum(claims)+sum(reserve))/ sum(premium)

               

              and each time you want a different combination it will take another field

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.