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

Table Calculations

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

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

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

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

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

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

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

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

Thanks Shin for making me understand different concepts.

• 9. Re: Table Calculations

You are welcome.

Shin

• 10. Re: Table Calculations

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

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