
1. Re: Circular reference
swaroop.gantela Jun 15, 2016 9:50 PM (in response to Jane Smith)Jane,
You were on the right track, but I think that using PREVIOUS_VALUE instead of LOOKUP
will get you there:
PREVIOUS_VALUE vs LOOKUP([expr],1) PREVIOUS_VALUE vs LOOKUP([expr],1)
For BeginningAmount2, I used
IF FIRST()=0
THEN SUM([Beginning Amount])
ELSE PREVIOUS_VALUE(0)+LOOKUP(SUM([Amount Received][Amount Spent]),1)
END
and for EndingAmount, I used
IF FIRST()=0
THEN SUM([Beginning Amount])+SUM([Amount Received][Amount Spent])
ELSE [BeginningAmount2]+SUM([Amount Received][Amount Spent])
END
Both were set with a "Compute using" of [Date]
I put the initial Beginning Amount in the dataset.

209117end.xlsx 9.2 KB

209117end.twbx 25.6 KB


2. Re: Circular reference
Mark Fraser Jun 16, 2016 12:12 AM (in response to Jane Smith)1 of 1 people found this helpfulHi 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!
Assuming Swaroop is correct, please mark his answer as such.
Cheers
Mark

3. Re: Circular reference
Jane Smith Jun 16, 2016 7:04 AM (in response to swaroop.gantela)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?
Thank you for your help

4. Re: Circular reference
swaroop.gantela Jun 16, 2016 12:19 PM (in response to Jane Smith)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

209117endB.twbx 46.1 KB

209117endB.xlsx 9.3 KB


5. Re: Circular reference
Jane Smith Jun 16, 2016 12:03 PM (in response to swaroop.gantela)That worked perfectly Swaroop!!
Thank you so much! I was starting to get worried that it wasn't possible.

6. Re: Circular reference
swaroop.gantela Jun 16, 2016 12:18 PM (in response to Jane Smith)Jane,
Glad it worked out.
All the best.

7. Re: Circular reference
David. Lewis Jun 22, 2016 4:06 PM (in response to Jane Smith)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

209117endB.twbx 39.9 KB


8. Re: Circular reference
swaroop.gantela Jun 22, 2016 4:47 PM (in response to David. Lewis)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?

209117endC.twbx 45.0 KB


9. Re: Circular reference
David. Lewis Jun 22, 2016 4:51 PM (in response to swaroop.gantela)Swaroop: Excellent catch. I missed that, obviously. Many thanks!