9 Replies Latest reply on Jul 25, 2018 10:14 PM by Zhouyi Zhang

    [Help] Using Value From a Single Cell in a Grand Total Column

    Jim Xu

      Hi all,

       

      I am trying to program a calculated field for an aggregated forecast accuracy rate for a report. This rate involves my aggregating all values in the Absolute Error column and dividing the result with the total Actuals value. I am only using total values, so all relevant data in the completed report will be in the Grand Total columns (auto-calculated by Tableau).  The aggregated forecast accuracy rate is in the Grand Total cell in the Forecast Accuracy column, and I have written a program to manipulate that cell. In the image below, the 64.14% is the Grand Total cell of the Forecast Accuracy column.

      Forecast Accuracy Rate Tableau.PNG

      However, the total of the Absolute Error column (ABS([Error])) is just the absolute error of the total of the Error column, so it's not correct. I have used WINDOW_SUM to program a table calculation that sums all the rows of the Absolute Error column.

      Total Absolute Error Tableau.PNG

      I currently have the 64.14% calculated from manually inputting 53,711,626 in the calculated field. [Feb-Apr Forecast Accuracy] is a calculated field that is used to calculate the accuracy rate for individual items.

      Manual Total Forecast Accuracy Tableau.PNG Is there way to automate this process by pulling the value from a cell of that column so that it's not hard-coded?

       

      Thank you so much!