11 Replies Latest reply on Mar 21, 2017 12:04 PM by Shinichiro Murakami

    Table Calculations

    satyavathi alluri

      Hi Everyone,

          I am new to tableau and need your help in understanding the table calculations in depth. I attached my sample workbook. In this workbook I created two calculated fields opening balance and closing balance and a parameter for the user to enter the opening balance. For Jan month the opening balance will be the parameter value and the closing balancing will be opening balance + deposit. For the next month Feb the opening balance will be the closing balance of the previous month and so on. When I write the below logic in the opening balance it is giving me circular reference errors for both opening balance and closing balance. Please let me know how to resolve the circular reference errors.

      Logic in the opening balance:

      IF [Opening Balance]=0 THEN [Please Enter The Opening Balance]

      ELSE [Opening Balance]=ZN(LOOKUP([Closing Balance],-1))

      END

       

      Regards.

        • 1. Re: Table Calculations
          Shinichiro Murakami

          Hi Satyavathi

           

          Yes, Table calculation is tricky.

          Below three formulas will bring same results, but option 2 might be easier to understand the logic, I think.

           

          running_sum combined with IF statement.

          first() only applied to first row of month.

           

          "Lookup" is not accumulating from beginning of month..

           

          1.

          [Closing_SM]

          if first()=0 then [Please Enter The Opening Balance]+sum([Deposit])

          else sum([Deposit])+previous_value(0) end

           

          or

           

          2.

          [Closing_SM 2]

          running_sum(if first()=0 then [Please Enter The Opening Balance]+sum([Deposit])

          else sum([Deposit]) end)

           

          or

          3.

          [Closing_SM 3]

          window_sum((if first()=0 then [Please Enter The Opening Balance]+sum([Deposit])

          else sum([Deposit]) end),first(),0)

           

           

           

           

           

          Thanks,

          Shin

          • 2. Re: Table Calculations
            Jamieson Christian

            1. [Closing_SM]

            if first()=0 then [Please Enter The Opening Balance]+sum([Deposit])

            else sum([Deposit])+previous_value(0) end

             

            Can this be written more simply as…

             

            sum([Deposit])+previous_value([Please Enter The Opening Balance])

             

            …or is there a caveat to doing so? I haven't used PREVIOUS_VALUE() much, but it seemed to yield equivalent results when I tested it on your example workbook.

            • 3. Re: Table Calculations
              Shinichiro Murakami

              You are right.

              It brings same results.  Thank you for input.

              I usually make trial and errors many times and just stop at the point the formula works and sometimes lazy to make it simplest.

               

              Thanks,

              Shin

              • 4. Re: Table Calculations
                satyavathi alluri

                Thanks Shin and Jamieson for taking time and replying to my question. Shin you have done all the calculations in the closing balance. But the thing is that I want the opening balance of Feb. month to be replaced with the closing balance of Jan month. To be more clear I want the output as below:

                 

                Month             Opening Balance                                          Deposit                        Closing Balance        

                Jan                    1000  (parameter value)                                200                              1200

                 

                Feb                   1200 (which is the closing                              300                              1500

                                                    value of jan month)                                                                                    .

                      

                I tried PREVIOUS_VALUE(ATTR([Closing Balance])) and also LOOKUP([Closing Balance],-1) which is throwing error as circular reference error. What else can I do to achieve the required output?

                • 5. Re: Table Calculations
                  Shinichiro Murakami

                  Here you go

                   

                  [Openning Balance SM]

                  if first()=0 then [Please Enter The Opening Balance]

                  else lookup([Closing_SM],-1) end

                   

                   

                  Thanks,

                  Shin

                  • 6. Re: Table Calculations
                    satyavathi alluri

                    Thanks Shin. It worked.

                    Now I am trying to add one more condition to this view. I want to display only those months based on my selection in the parameter2. When I do this every time the opening balance is replaced with parameter value which is 2000 for that selected month instead of showing the actual opening balance.How to avoid the overwriting? Attached the workbook.

                     

                    Regards.

                    • 7. Re: Table Calculations
                      Shinichiro Murakami

                      Satyavathi,

                       

                      Here you go.

                      Filter should not filter data, but only "Display".

                       

                      In this case, create aggregated calculated filed as quick filter help to create filter which keep table structure with filtering only display.

                      You can select multiple months with this, while Parameter does not allow multiple selections.

                       

                      [Month Selection (copy)]

                      lookup(min([Month]),0)

                       

                       

                      BTW, Could you mark my answer as correct.

                       

                       

                      Thanks,

                      Shin

                      • 8. Re: Table Calculations
                        satyavathi alluri

                        Thanks Shin for making me understand different concepts.

                        • 9. Re: Table Calculations
                          Shinichiro Murakami

                          You are welcome.

                           

                          Shin

                          • 10. Re: Table Calculations
                            kumar.c

                            Hi Shinchiro,

                             

                            I have a similar situation, but little bit different and I am not using parameter to enter values and the values are coming from three measure fields.

                             

                            Please find the attached excel and also i have logic in place for every Balance column and also for Orders column from Feb month if you see the cell formula.

                             

                            Need help here.

                            • 11. Re: Table Calculations
                              Shinichiro Murakami

                              Hi Kumar

                               

                              Just did quickly.

                               

                              [Balance Calc]

                              if first()=0 then sum(ifnull([Orders],0)+ifnull([V1],0)-ifnull([V2],0))

                              else previous_value(0)+sum(ifnull([V1],0)-ifnull([V2],0)) end

                               

                               

                              Thanks,

                              Shin