3 Replies Latest reply on Apr 16, 2018 7:56 AM by Okechukwu Ossai

    Profit & Loss Statement Calculated Field Year to Date Calculation

    David Luhut

      Hello,

       

      I am new to Tableau so please be patient with me.

       

      Also, I can't share the workbook because my data is directly 'linked' to SAPBW backend. Therefore, my data contains sensitive data.

       

      What I am trying to do here is to create a P&L statement and I understand that Tableau is more of a visual tool rather than a 'text layout' purposes.

       

      After many trial and errors, I was able to replicate the P&L Statements just as how I was able to create in PowerPivot. For example, having the 'Operating Income' and 'Building Income' (shown below in green).

       

      Also, due to Accounting thingy, anything that's negative number is actually "good" and anything that's positive is actually "bad". And since users like to see everything in 'positive' numbers, I create a CASE statement that reverse the sign when it's 'Revenue', 'Operating Income', 'Building Income' etc, and leave the Expense side as is, reflecting the PnL Header Categories.

       

      MTD Actual:=

       

      CASE ATTR([PnL Header])

          WHEN "Revenue"

              THEN -SUM([Local Actual])

          WHEN  "Operating Income"

              THEN -SUM([Local Actual])

          WHEN  "Building Income"

              THEN -SUM([Local Actual])

          WHEN  "Net Income (Loss)"

              THEN -SUM([Local Actual])

          ELSE SUM([Local Actual])

      END

       

      where [Local Acutal] comes from the SAPBW column itself.

      My problem now is the YTD calculation. Somehow it always calculate from Jan to Dec, instead of Jan to User selected month (Shown below in red).

      For example, if I were to select 'Oct' on the 'Month' filter, the YTD Actual and YTD Budget stays the same (shown in red).

       

      I have tried changing different LOD statements, and I am still not able to get the correct result.

      Below is what I have for my YTD Actual calculation.

       

      YTD Actual:=

       

      { FIXED [PnL Header], [SubHeader], [Lower SubHeader], [Fiscal Year], [Ownership], [City], [Entity] :

          SUM(IF [Calc YTD Actual] = TRUE AND

                  ([PnL Header] = "Revenue" OR

                   [PnL Header] = "Operating Income" OR

                   [PnL Header] = "Building Income" OR

                   [PnL Header] = "Net Income (Loss)")

              THEN -[Local Actual]

              ELSE [Local Actual]

              END

         )

      }

       

      where [Calc YTD Actual] calculated field is

       

      [Date] >= DATE('01/01/'+[Fiscal Year]) AND [Date] <= DATE(STR([Period])+'/01/'+[Fiscal Year])

       

      Year To Date Error Calculation.png

       

      So what's the solution to my YTD value and reflecting correctly to the 'PnL Header', 'SubHeader' and 'Lower SubHeader' with respect to the filters?

        • 1. Re: Profit & Loss Statement Calculated Field Year to Date Calculation
          Okechukwu Ossai

          Hi David,

           

          It is difficult to provide a specific solution by just looking at an image. For example, I can't see the formula behind Fiscal Year, filterMonth and Period. However, it seems you need to use parameters instead of filters. The parameters can be linked to the underlying data using calculated fields.

           

          One approach will be to create a parameter called [Year]; possibly a list of strings or integers 2016, 2017, 2018. Remember that parameters are not dynamic, so you will have to update the list manually in 2019. Alternatively, you could allow the users to type in the year instead. You can even use range option in the allowable values and use a slider to select integer years. It's all up to you.

           

          First step: Remove [Fiscal Year] and [filterMonth] from the filter shelf.

           

          Second step: Create a parameter called [Year]

           

          Third step: create a second parameter called [Month].

          Make it a list of string with values as 01, 02, 03, 04, .....11, 12 and display as Jan, Feb, Mar, etc

          After creating the parameters, right click on each parameter and select 'Show Parameter Control'.

           

          Fourth step: Create a calculated field called [Local Actual Year Filtered].

          IF MONTH([Date]) >= 1 AND MONTH([Date]) <= INT([Month]) AND YEAR([Date]) = INT([Year]) AND [Year] = [Fiscal Year] THEN [Local Actual] END

          Since I don't know how Fiscal Year, filterMonth, Period and Date relate to each other, this formula might not be optimal. Try this first, and if it doesn't work correctly, feel free to adjust this to suit your dataset. Alternatively, post the formulas so I can modify the calculated field for you. I assumed [Fiscal Year] is a string. If it is an integer then use 'AND INT([Year]) = [Fiscal Year]'

           

          Fifth step: Create a calculated field called [Local Actual Month Filtered].

          IF MONTH([Date]) = INT([Month]) AND YEAR([Date]) = INT([Year]) AND [Year] = [Fiscal Year] THEN [Local Actual] END

          Same comment as above.

           

          Sixth step: Modify [MTD Actual].

          Replace [Local Actual] with [Local Actual Month Filtered]. Repeat this for [MTD Budget].

          CASE ATTR([PnL Header])

              WHEN "Revenue"

                  THEN -SUM([Local Actual Month Filtered])

              WHEN  "Operating Income"

                  THEN -SUM([Local Actual Month Filtered])

              WHEN  "Building Income"

                  THEN -SUM([Local Actual Month Filtered])

              WHEN  "Net Income (Loss)"

                  THEN -SUM([Local Actual Month Filtered])

              ELSE SUM([Local Actual Month Filtered])

          END

           

          Seventh step: Modify [YTD Actual].

          Replace [Local Actual] with [Local Actual Year Filtered]. Repeat this for [YTD Budget]

          { FIXED [PnL Header], [SubHeader], [Lower SubHeader], [Fiscal Year], [Ownership], [City], [Entity] :

              SUM(IF [PnL Header] = "Revenue" OR

                       [PnL Header] = "Operating Income" OR

                       [PnL Header] = "Building Income" OR

                       [PnL Header] = "Net Income (Loss)"

                  THEN -[Local Actual Year Filtered]

                  ELSE [Local Actual Year Filtered]

                  END

             )

          }

           

          Hope this helps.

          Ossai

          1 of 1 people found this helpful
          • 2. Re: Profit & Loss Statement Calculated Field Year to Date Calculation
            David Luhut

            Thanks Okechukwu Ossai for the help.

             

            Really appreciate it.

             

            I thought using the filter could dynamically update the year itself.

             

            But it does solves my problem.

             

            Thank you!