3 Replies Latest reply on Oct 24, 2018 12:56 PM by venkatram

    Assisting in Subtracting in a pivot

    Eugene Trilesnik

      Hey Guys,

       

      I am trying to figure out how to subtract some interesting notice. I have attached the Tableau workbook and excel document. I would like to take the forecast numbers under Version = Forecast and Account = Net FTE Enrollment and subtract from Version = 18-19 Account = Net FTE Enrollment. The problem I am running into is that the columns are also different for both of those things. If the data was copied down I could do it but the data is in this format and I want to keep it standardized. An Example of what number I want to see is.

       

      Campus = Test 1

      Variance (Thing im looking for) = 68 (forecast) - 45.6 (PS-PK Oct 2018) = 22.4

       

       

      And I want it to look like

       

      Test 1      Test 2                          Test 3 ....

      22.4          (other number)          ....

        • 1. Re: Assisting in Subtracting in a pivot
          venkatram

          Hey Trileshik,

           

          Tableau is not good for Cross Tabs and supposted only tabular excel files. Please work on your excel file and you might help on this issue.

           

          Base on your file, I noticed you might need more adjustment in the excel file before working in Tableau.

           

          Sample Working file looks like this.

           

          Its suppose to be like this in you excel file.

           

          Check this link for better understanding excel formatting.

           

          Reshaping Crosstab Data For Use in Tableau - YouTube

           

          Data Reshaping in Tableau 9 - data interpreter & data pivot - YouTube

                

          VersionCampusAccountNameActual/Forvalue
          18-19Test1Net FTE EnrollmentPS-PK Oct 2018Actual46
          18-19Test1Available CapacityPS-PK Oct 2019Actual11
          18-19Test1Withdraw FTEPS-PK Oct 2020Actual1
          17-18Test1Net FTE EnrollmentPS-PK Oct 2021Actual0
          17-18Test1Available CapacityPS-PK Oct 2022Actual0
          17-18Test1Withdraw FTEPS-PK Oct 2023Actual0
          16-17Test1Net FTE EnrollmentPS-PK Oct 2024Actual0
          16-17Test1Available CapacityPS-PK Oct 2025Actual0
          18-19Test1Net FTE EnrollmentKG Oct 2018Actual13
          18-19Test1Available CapacityKG Oct 2019Actual9
          18-19Test1Withdraw FTEKG Oct 2020Actual0
          17-18Test1Net FTE EnrollmentKG Oct 2021Actual0
          17-18Test1Available CapacityKG Oct 2022Actual0
          17-18Test1Withdraw FTEKG Oct 2023Actual0
          16-17Test1Net FTE EnrollmentKG Oct 2024Actual0
          16-17Test1Available CapacityKG Oct 2025Actual0
          18-19Test1Net FTE Enrollment1G-5G Oct 2018Actual19
          18-19Test1Available Capacity1G-5G Oct 2019Actual25
          18-19Test1Withdraw FTE1G-5G Oct 2020Actual0
          17-18Test1Net FTE Enrollment1G-5G Oct 2021Actual0
          17-18Test1Available Capacity1G-5G Oct 2022Actual0
          17-18Test1Withdraw FTE1G-5G Oct 2023Actual0
          16-17Test1Net FTE Enrollment1G-5G Oct 2024Actual0
          16-17Test1Available Capacity1G-5G Oct 2025Actual0
          18-19Test1Net FTE EnrollmentPS-PK ForecastForecast68
          18-19Test1Available CapacityPS-PK ForecastForecast68
          18-19Test1Withdraw FTEPS-PK ForecastForecast68
          17-18Test1Net FTE EnrollmentPS-PK ForecastForecast68
          17-18Test1Available CapacityPS-PK ForecastForecast68
          17-18Test1Withdraw FTEPS-PK ForecastForecast68
          16-17Test1Net FTE EnrollmentPS-PK ForecastForecast68
          16-17Test1Available CapacityPS-PK ForecastForecast68
          • 2. Re: Assisting in Subtracting in a pivot
            Eugene Trilesnik

            Hi Venkatram,

             

            I use the pivot function in Tableau for that! I just pivot all the columns into it. So there is no way to do it in tableau natively for the subtraction?

             

            Best,

            Eugene

            • 3. Re: Assisting in Subtracting in a pivot
              venkatram

              hey Trilesnik,

               

              Sorry, I dont have 10.5 version to view your tableau report. If you dont mind, can you share me screenshots of Data Preparations using pivot .?