2 Replies Latest reply on Jul 31, 2016 11:13 PM by lei.chen.0

    How to create a Running Calc for an Aggregate Measure

    Ramesh Krishnan

      Hello Helpers,

       

      I have investment and return data for each month since Jan 15.

      I want to report quarterly % returns. So, I use a calculated field to calculate % Return as sum(return)/sum(investment). Till this it works perfect.

       

      Now, I want to see YTD % Returns by quarter. For example, the number for Q1 2015 should show just the % return for Q1 2015, i.e., 10.32%. But, for Q2 2015, it should show the overall % returns for both Q1 and Q2 2015, i.e, 12.79%. But, when I try to add the Running Total table calc, Tableau asks me to choose an aggregation method.

      This means, Tableau will calculate the % Return for Q1 2015 (10.32%) and Q2 2015 (14.94%) and then apply this aggregation. At best, I can choose Average, but that will result in 12.63%. But this is wrong, because it should have been 12.79%.

       

      How can I get Tableau to follow the actual aggregation method (sum(return)/sum(investment)) even while calculating Running Totals? I am attaching the workbook for your reference.

       

      Thanks,

      Ramesh