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

# Cannot total complex Lookup() table calculation over categorical dimension

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 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: 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)
• ###### 1. Re: Cannot total complex Lookup() table calculation over categorical dimension

Hi,

I did not analyze your calcs thoroughly - so I may had it totally wrong - anyway I modified the following to get some results on "Cumulative CAPEX WDV at Year Close":

• in [Cumulative WDV pt1] I replaced all ATTR([Asset Class Name]) with MIN([Asset Class Name]) - and I suggest never to use ATTR in calculations
• differentiate the outer calc for totals: Cheers,

Łukasz

1 of 1 people found this helpful
• ###### 2. Re: Cannot total complex Lookup() table calculation over categorical dimension

Thanks for your advice Łukasz Majewski that helped push me along to the next step, I changed all of the ATTR() calcs with MIN() in both the pt1 calc and pt2 calc. Doing this has allowed the Table Column Totals to naturaly show, now that I can see them it is obvious that there is an issue with the partitioning of the column totals but I am unsure of how to fix it:

• Set the [Cumulative WDV pt1] Default Properties > Total Using > SUM
• Now with this measure on the table the total row correctly aggregates the asset class values by year
• Because [Cumulative WDV pt2] contains the LOOKUP() and PREVIOUS() table calcs I cannot set the Total Using in the default properties
• The Column Total for this is still aggregating to the wrong amount (guessing along an unexpected partition) but I cant work out what the partition is or how to fix it to the correct partition (I think it should be pane down)
• I have noticed that changing the Total Using in [Cumulative WDV pt1] changes the result of the Column Total for [Cumulative WDV pt2]

As far as I can tell, solving how [Cumulative WDV pt2] is aggregated into the column total will resolve the rest of my issue.. But i'm completely stumped onhow to do this - I have tried applying WINDOW_SUM() or FIXED LODs within the pt2 calculation but am not getting anywhere with that.

I have attached an updated workbook for reference

Thanks again

Andy

• ###### 3. Re: Cannot total complex Lookup() table calculation over categorical dimension

So, again I cannot spend too much time on this so not sure what these calculations do and whet the expected results are, but I think you should customize your totals using additional copy of one of the dimension (here I think it is [Asset Class Name]): Please let us know what values you expect in standard rows and totals.

• ###### 4. Re: Cannot total complex Lookup() table calculation over categorical dimension

Thank you Łukasz Majewski!

As you can probably tell I had not previously used Table Calcs beyond the basics so this has been a very useful learning curve.

There is one component that I don't fully understand - when creating the dimension copy (in this case [Asset Class Name (Copy)]) how do the the First() and Last() calcs count differently over the dimension versus the original? Is it simply that it is not on the canvas so therefore counts over them as it appears in the data source table?

Regards

Andy

• ###### 5. Re: Cannot total complex Lookup() table calculation over categorical dimension

Hi,

Your calculations use a complex algorithm of referencing dimensions and parameters. When tableau calculates total for a dimension that dimension "collapses" and you can no longer reference them individually and only aggregations like min(asset dim) could be used. Therefore one way to work around this is to introduce a copy of that dimension in details since in column total row the dimension values from details do not "collapse" and still can be used for partitioning/addressing table calculations.