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

    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])



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



      DateBeginning AmountAmount ReceivedAmount SpentEnding AmountActual Beginning AmountActual Ending Amount
        • 1. Re: Circular reference



          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)



          and for EndingAmount, I used

          IF FIRST()=0

          THEN SUM([Beginning Amount])+SUM([Amount Received]-[Amount Spent])

          ELSE [BeginningAmount2]+SUM([Amount Received]-[Amount Spent])



          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
            Mark Fraser

            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!

            Assuming Swaroop is correct, please mark his answer as such.




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

              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



                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:


                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 to the predicted delta:


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

                THEN SUM([Actual Ending Amount])

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


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

                  That worked perfectly Swaroop!!


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

                  • 6. Re: Circular reference



                    Glad it worked out.

                    All the best.

                    • 7. Re: Circular reference
                      David. Lewis

                      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


                      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)



                      and CalcEnding as


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

                      THEN SUM([Ending Inv])

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




                      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



                        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)



                        It seemed to be working better.

                        Are there nulls in your original dataset?



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

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