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)

      end

       

      Beginning Amount:

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

      else attr([Actual Beginning Amount])

      end

       

      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
      Mar-1630102383038
      Apr-1638715303830
      May-1630412223022
      Jun-16221813922
      Jul-163961035
      Aug-16354831
      Sep-163151521
      Oct-16218722
        • 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:

          CalcBeginning

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

          THEN SUM([Actual Beginning Amount])

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

          END

           

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

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

          CalcEnding

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

          THEN SUM([Actual Ending Amount])

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

          END