5 Replies Latest reply on Jul 10, 2018 10:31 PM by Shinichiro Murakami

    Running Sum calculation with an anchor amount.

    Daniel Cavenagh

      Hello all,


      I'm hoping you fine Tableau community people will be able to assist with my problem. I've had a good whack at it myself and combed through the forums but haven't found a solution. Apologies if it is something simple and due to my own user error and thank you in advance.


      I have data for a different team's cashflow forecasts. The data is presented as a forecast movement rather then a cumulative position.


      I then have the actual position which has the movement and the cumulative function.


      I want to create a line graph that shows the actual position over time and then the forecast position for the next two months.


      I have created a calculation that uses the Running Sum function with the forecast movement to show the cumulative position. Then I have set up parameters to filter the line to show the relevant dates.


      IF (ATTR([Date])>= [Parameters].[Latest Business Date]+1 AND ATTR([Date]) <= [Parameters].[Last Day of Next Month]) THEN

      RUNNING_SUM(SUM([Forecast Movement])) END.


      My problem is that the lines don't match at the swap-over point.


      forecast ahead snap.png


      If I change the calculation to be


      IF (ATTR([Date])>= [Parameters].[Latest Business Date]+1 AND ATTR([Date]) <= [Parameters].[Last Day of Next Month]) THEN

      XXXXX + RUNNING_SUM(SUM([Forecast Movement])) END.


      and make XXXXX the absolute difference between the two points then it works fine but I would prefer a more dynamic version that updates automatically.


      I've tried to make a calculation and a parameter to produce that number but then line graph turns into a stationary point.


      I'm sure I lack some understanding of how Tableau registers this data and any help would be appreciated.


      Also if there is a better way to go about the task then I am open to suggestions. Also, I am unable to alter the data source.


      Tableau 10.5.2 by the way.