5 Replies Latest reply on Dec 9, 2016 1:48 AM by Łukasz Majewski

    Cannot total complex Lookup() table calculation over categorical dimension

    Andrew Prince

      Edited original post to attach solution workbook: (Forecast_Tool - Solution)

       

      I have been working on this for a couple of days but cannot figure how to get a total of the depreciated cumulative asset class values for each year.

      Advice, solutions or different approaches will be much appreciated!

       

      Background:

      • The attached workbook contains a cut down version of a forecasting model.
      • The subject of the forecast is depreciation of assets over time plus the forecast CAPEX investment to replace or increase those assets.
      • Clicking the hamburger icon in the 'Forecast Tool' dashboard reveals all of the user input parameters
      • The intention is to allow the end user to input the forecast capital expenditure in any of the next 10 years if they do not like the base forecast (calculated in the data-set)
        • Each year's expenditure is split into 'Asset Classes' such as Buildings, Property & Equipment, etc. Each of these asset classes has a different Depreciation Rate that can be input by the user

      Forecast Tool Menu.jpg

      Primary Issue:

      • The calculation for the each year's closing Written Down Value (WDV) (the depreciated value) is:
        • For each year and for each individual Asset Class:
          • (Prior Year WDV + Current Year Forecast CAPEX) - (Prior Year WDV + Current Year CAPEX) * Asset Class Depreciation Rate
      • Due to the user inputs I would like to include in the model, it appears that the only way to calculate the above is by using the following Table Calculation (named [Cumulative WDV pt2] in the workbook)  :

      "IF [AC1 Parameter] = ATTR([Asset Class Name])

          THEN

              ZN(LOOKUP([Cumulative WDV pt1],-1) + PREVIOUS_VALUE(-1))

                  -

              (ZN(LOOKUP([Cumulative WDV pt1],-1) + PREVIOUS_VALUE(-1)) * MIN([AC1 Depn Rate Input]))

       

      ... END"

      • However, because I am calculating the value individually along the Asset Class categorical dimension I am not able calculate the total (all asset classes) of each year's closing Written Down Value.
      • Solving this is important so that I can use the total to calculate the year on year change in the Asset Base
      • As you can see in the below image I am forced to include the Asset Class dimension on the Details shelf:

      Cumulative WDV Chart.PNG

       

       

      Other Constraints:

      • Due to security concerns the workbook must be fully packaged with extracts (distributed via Tableau Server) so no parameters + custom SQL allowed.
      • It is important that the end user can tweak aspects of the model - therefore the large number of input parameters (and the above question)
      • Solution or approach must be available in Tableau version 9.0 (annoying I know)