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

conditional logic/circular reference

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

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.

 Date Beginning Amount Amount Received Amount Spent Ending Amount Actual Beginning Amount Actual Ending Amount Mar-16 30 10 2 38 30 38 Apr-16 38 7 15 30 38 30 May-16 30 4 12 22 30 22 Jun-16 22 18 1 39 22 Jul-16 39 6 10 35 Aug-16 35 4 8 31 Sep-16 31 5 15 21 Oct-16 21 8 7 22
• 1. Re: conditional logic/circular reference

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