9 Replies Latest reply on Jun 22, 2016 4:51 PM by David. Lewis

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

I'm new to calculated fields so I'd appreciate any advice

 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: Circular reference

Jane,

You were on the right track, but I think that using PREVIOUS_VALUE instead of LOOKUP

will get you there:

For BeginningAmount2, I used

IF FIRST()=0

THEN SUM([Beginning Amount])

END

and for EndingAmount, I used

IF FIRST()=0

END

Both were set with a "Compute using" of [Date]

I put the initial Beginning Amount in the dataset.

2 of 2 people found this helpful
• 2. Re: Circular reference

Hi Jane

To help understand Swaroops's solution using the INDEX() function, would show you the row numbers.

I'll explain -

He uses FIRST() == 0 i.e. if the row number is 0, i.e. its the first row, its a way of anchoring the calculations.

FYI, there is also a LAST() function, which works the opposite way, as you would expect

Once anchored (via FIRST()) you can get the other figures, in this case using the previous value function.

Hope that helps!

Cheers

Mark

1 of 1 people found this helpful
• 3. Re: Circular reference

Hi Swaroop,

Thank you for responding to my question but I could still use some help. I left out explaining a critical part of the issue when I wrote the question. In your solution you removed the Actual Beginning Amount and Actual Ending Amount variables because I hadn't explained why they were necessary. 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. Our subsequent 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. With this additional information do you know how I could reformat the solution you gave me?

• 4. Re: Circular reference

Jane,

Sorry for not having caught that.

Attached is another attempt, not sure if it's quite there.

I've added a few more columns, but they may not be correct.

Please correct them and repost as needed.

This may not be right, but my assumption is that what is known are

the values in the past and the predicted receive and spend numbers.

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

use that, otherwise add the Previous Beginning Amount to 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 to the predicted delta:

CalcEnding

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

THEN SUM([Actual Ending Amount])

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

END

2 of 2 people found this helpful
• 5. Re: Circular reference

That worked perfectly Swaroop!!

Thank you so much! I was starting to get worried that it wasn't possible.

• 6. Re: Circular reference

Jane,

All the best.

• 7. Re: Circular reference

Hi Swaroop, Jane:  I'm happy to find this thread because it is precisely what I've been trying to solve.  I downloaded the solution you posted, and tried to duplicate it precisely but have not gotten it to work.  I've created a third sheet in your twbx, with some sample data.  The data looks something like

date

starting inventory (only has a value for the first row)

packaging quantity (additions to inventory for that day)

order quantity (depletions from inventory for that day)

hold quantity (another type of addition to inventory for that day)

ending inventory (calculated for the first day only)

This is more or less a subset of the columns in the solution you posted.

Basically I'd like for each day to take the starting inventory + packaging + holds - orders = calculated ending inventory

Starting inventory for the next day is Calculated Ending Inventory for the previous day.

To achieve this I created CalcBeginning as

IF NOT(ISNULL(SUM([Starting Inventory])))

THEN SUM([Starting Inventory])

ELSE PREVIOUS_VALUE(0)+LOOKUP(SUM([Holds]+[Orders]+[Packaging]),-1)

END

and CalcEnding as

IF NOT(ISNULL(SUM([Ending Inv])))

THEN SUM([Ending Inv])

ELSE [CalcBeginning]+SUM([Holds]+[Orders]+[Packaging])

END

This is precisely what you've done.  However it will not cooperate with my sheet and formulas.  I've checked everything I can, but nothing seems to work.  Any advice would be very much appreciated.  David

1 of 1 people found this helpful
• 8. Re: Circular reference

David,

This may just be an artifact of how your data got pasted in via the Clipboard,

but it seemed like there were zeroes (versus nulls) for the other beginning amounts.

So when I used:

IF SUM([Starting Inventory])<>0

THEN SUM([Starting Inventory])

ELSE PREVIOUS_VALUE(0)+LOOKUP(SUM([Holds]+[Orders]+[Packaging]),-1)

END

It seemed to be working better.

Are there nulls in your original dataset?

1 of 1 people found this helpful
• 9. Re: Circular reference

Swaroop:  Excellent catch.  I missed that, obviously.  Many thanks!