5 Replies Latest reply on Mar 7, 2016 11:46 AM by Tom Welch

    How can I use calculated fields to pivot data?

    Tom Welch

      How would I do the following pivot?  I will eventually want to do some math between the resultant pivoted fields.

       

      I have records that look like this:

           

      OPERATING_DATESETTLEMENT_DATECompany NameVersionBILL_DETDAILY_TOTAL
      11/11/2015 0:0011/18/2015 0:00Company A4TYPE117158.68
      11/11/2015 0:0011/18/2015 0:00Company A4TYPE3517.2
      11/11/2015 0:0011/18/2015 0:00Company A105TYPE22165.02
      11/11/2015 0:0011/18/2015 0:00Company A55TYPE22165.72

      and want to have them look like this:

       

      Row Labels471455105
      11/11/201517675.882220.472210.492165.722165.02
      Company A17675.882220.472210.492165.722165.02
      11/12/201518664.831327.921330.51358.37
      Company A18664.831327.921330.51358.37
        • 1. Re: How can I use calculated fields to pivot data?
          ann.stolzman

          Tom,

          How about something like this?  When you bring your data into Tableau, you'll want to change Version to a Dimension (just right click), and your date field will have to adjust to show the full date (another right click|format sort of thing).  Then add subtotals (but they're going to be at the end of the segment, and not at the top).  I edited the sample data that you gave in your post to be able to have two dates.

           

          Does that help?

           

          -Ann-

          Calc to pivot.PNG

          • 2. Re: How can I use calculated fields to pivot data?
            Tom Welch

            Ann,

            This helps quite a bit. (I’ve been trying to reply on the community page, but my browser has a problem with Tableaus site for some reason)

             

            Now if I wanted to subtract columns 105 from 55 and display the results in a column at the end, how would I do that? ( I’m kind of a newbie)

             

            Thanks,

            Tom Welch

            Manager of Data Services

             

            Grace, it's a name for a girl.

            It's also a thought that changed the world - Bono

             

            P Please consider the environment before printing this email.

            • 3. Re: How can I use calculated fields to pivot data?
              pooja.gandhi

              Hi Tom!

               

              So first of all, you already have pivoted data if you drag version to dimensions and group the daily total by date, company and version. For the task you want to achieve, you are actually asking for un-pivoted data, best achieved when versions are listed in different columns versus is a single column with corresponding daily total. If you have a whole lot of versions, this might be a troublesome way to things in Tableau. But I will still, go ahead and explain incase it works for your needs. I would drag version to dimensions and create 4 calcs, so that versions become different columns:

               

              4: if [Version] = 4 then [Daily Total] end

              55:if [Version] = 55 then [Daily Total] end

              105: if [Version] = 105 then [Daily Total] end

              Subtraction:sum([55]) - sum([105])

               

              From here, you basically drag date and company name on rows and double click on all of these calcs to bring them on the view. You can turn on grand totals/sub totals from the analysis menu up top.

               

               

              Hope this helps. The way you have data set up right now, is the ideal way to have it, but because you need the last column of subtraction, this is one way to go about it!

               

              Pooja.

              • 4. Re: How can I use calculated fields to pivot data?
                pooja.gandhi

                Oh and if you have access to Tableau 9.2 or higher you can actually move your grand totals to top:

                 

                2 of 2 people found this helpful
                • 5. Re: How can I use calculated fields to pivot data?
                  Tom Welch

                  Thank you all. This worked great.