1 Reply Latest reply on Jun 16, 2016 12:06 PM by Jane Smith

    conditional logic/circular reference

    Jane Smith

      Does anyone know how I could code Beginning Amount and Ending Amount in calculated fields in Tableau? The way I'm doing it is causing a circular reference error for the two.

      Here's how I am currently doing it.



      Ending Amount:

      if ISNULL ( attr([Actual Ending Amount])) then [Beginning Amount] + attr([Amount Received]) - attr([Amount Spent])

      else attr(Actual Ending Amount)



      Beginning Amount:

      if ISNULL (attr([Actual Beginning Amount])) then lookup([Ending Amount],-1)

      else attr([Actual Beginning Amount])



      Also to add more background:

      We start each month (for example June) off with knowing what our (Actual) Ending Amount was for the previous month (22), this number carries into the next month and defines the (Actual) beginning amount for that month. These are our actual known numbers. We then forecast what we think our amount spent, and amount received will be and come up with a predicted Ending Amount (39). Our subsequent monthly calculations are all based off of these numbers until we get to the end of June when we will know an Actual Ending Amount to replace the predicted amount with. We then update the predicted ending amount with the known actual ending amount to make the subsequent months calculations/predictions more accurate going forward. This is why I included the Actual Beginning and Ending Months (months that have happened and therefore supplied known values, blue) in the "if" "then" logic.


      I'm new to calculated fields so I'd appreciate any help.




      DateBeginning AmountAmount ReceivedAmount SpentEnding AmountActual Beginning AmountActual Ending Amount
        • 1. Re: conditional logic/circular reference
          Jane Smith

          For the Calculated Beginning, if there is an Actual Beginning Amount,

          use that, otherwise add the Previous Beginning Amount the predicted delta:


          IF NOT(ISNULL(SUM([Actual Beginning Amount])))

          THEN SUM([Actual Beginning Amount])

          ELSE PREVIOUS_VALUE(0)+LOOKUP(SUM([Pred Rcvd]-[Pred Spent]),-1)



          Likewise for the Calculated Ending, if there is an Actual Ending Amount,

          use that, otherwise add the Calculated Beginning the predicted delta:


          IF NOT(ISNULL(SUM([Actual Ending Amount])))

          THEN SUM([Actual Ending Amount])

          ELSE [CalcBeginning]+SUM([Pred Rcvd]-[Pred Spent])