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)

      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

       

           

      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: Circular reference
          swaroop.gantela

          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.png

          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.

             

            Cheers

            Mark

            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
                swaroop.gantela

                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
                  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
                    swaroop.gantela

                    Jane,

                     

                    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

                      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
                        swaroop.gantela

                        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.png

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

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