2 Replies Latest reply on Sep 10, 2018 1:03 AM by Paolo Cavatore

    How to aggregate calculated fields using table calculations

    Paolo Cavatore

      I'm struggling with the way Tableau aggregates calculated fields including table calculations.

       

      I attach the simplified workbook.

       

      Here is the data  source:

      It's a simple monthly breakdown of revenues and prices by product.

      The yellow column - PriceImp - was calculated in excel but is not available in my actual data source and is included here for comparison only.

      The Price Impact is the amount of revenue change due to a price change only and is defined as: (Price_t / Price_t-1 - 1) * Revenues_t-1

      Basically is the monthly % price change applied to revenues from the previous month.

       

      I created the calculated field "PriceImp CF" using table calculations in the following way:

      (ZN(SUM([Price])) / LOOKUP(ZN(SUM([Price])),-1) - 1) * LOOKUP(ZN(SUM([Revenues])),-1)

      computed along Months.

       

      The numbers I get do match the expected ones as per the comparison in the "PlainTable" worksheet (Price Imp vs PriceImp CF).

       

      So far so good. Now I need to aggregate (sum) the price impacts by months.

      This is the result I'm trying to achieve:

      The "MonthlyAggregation" worksheet is doing that but unfortunately "PriceImp CF" doesn't get aggregated as expected.

       

      In summary it seems that calculated fields WITHOUT table calculations do get aggregated whilst as soon as you include a table calculation (as per my need) the aggregation is no more performed.

       

      Am I missing something? Is there a way to come up with the right figures for the price impact by month?

        • 1. Re: How to aggregate calculated fields using table calculations
          Aaron Dobbins

          Hi Paolo,

           

          Are you looking for the running total of Price Impact as each month is displayed?  With your sample workbook I set the table calculation on Price Imp to Running Total, then dragged the Price Imp pill onto Measure Names again to get those results.

           

          1. Set Price Imp pill in Measure Names to Running Total.  You have to do this first because you cannot have the same pill in Measure Names twice, unless they are a different aggregation or have a table calculation.

           

           

          2. Drag Price Imp into Measure Names so you can display the Price Imp per month

          • 2. Re: How to aggregate calculated fields using table calculations
            Paolo Cavatore

            Hi Aaron Dobbins,

            thank you for your answer, unfortunately that's not what I am trying to achieve.

            First of all I am not looking for the running total of "PriceImp CF" but for the simple sum by month as reported in the second table of my original post.

            Secondly, and more relevant, I need to use the calculated field - "PriceImp CF" - rather than "Price Imp" as the later is not available in my original data source and is provided for comparison reasons only as indicated in my post.

            Thank you for taking the time anyway.