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.

       

      Thanks,

       

      Daniel