3 Replies Latest reply on Aug 7, 2018 12:10 PM by Mavis Liu

    calculated field on the fly at row level

    keerthana kumar

      Hi All,

       

      I have a report like below

      For a PARTICULAR Product what was the forecast value from Jan2018 to May2018 as well as the Business plan,Last year sales and Actually what is achieved for the month

      i have an issue where I have to calculate 201801Forecast Vs Business Plan and 201801Forecast Vs LY inside tableau because there are many filters im going to apply.

      How can i bring this calculation in rows

       

      Any help

        • 1. Re: calculated field on the fly at row level
          Mavis Liu

          Hi Keerthana,

           

          I believe that you'll need to re-structure your data a bit to get this.

           

          So in the data source window, control and select sales and stock, then right click to pivot:

           

          2018-07-30_15h26_24.png

           

          You should then get two new columns Pivot Field Names and Pivot Field Values, feel free to rename them. But essentially pivot field names now contains the text values of Sales or Stock, the the pivot values contain the values.

           

          Now we can use some LODs to work out the %. First I created two calculations, one for BP and one of LY:

           

          2018-07-30_15h44_41.png

           

          2018-07-30_15h45_12.png

           

          Then I created two more calculated fields to work out the %:

          2018-07-30_15h35_11.png

          2018-07-30_15h35_25.png

           

          Then I brought them into the view:

           

          2018-07-30_15h37_05.png

           

          With this method, the non period options (e.g. actuals, business plan) will still show all 3 metrics.

           

          So what you can do is create two sheets and combine them into one view:

           

          2018-07-30_15h42_56.png

           

          Thanks and please see attached.

           

          Mavis

          • 2. Re: calculated field on the fly at row level
            keerthana kumar

            Thanks Mavis,

            The approach was lot of help . Thanks for giving me an insight.

             

            In the below Sales Vs BP = Sales/BP *100 and number formatting %

                          but Stocks= Sales-BP and number formatting is standard with 2 decimal points

             

             

            How can we achieve this.

            • 3. Re: calculated field on the fly at row level
              Mavis Liu

              Hi Keerthana,

               

              You can right click on the field in the data pane and change the default properties for the number format.

               

              Or you can right click on the pill in measure values bucket and change the number format there.

               

              Thanks,

               

              Mavis