3 Replies Latest reply on Oct 27, 2015 6:02 AM by Shawn Wallwork

# How to create a row grand total for a Difference (Quick Calculation)?

Hello,

We have a numerical field 'UnrealizedPnL' which we used a 'Difference' quick table calculation for.  This works correctly.

So data, looks like this:

PortfolioJan 2011Feb 2011Mar 2011Apr 2011Total
A-123+144-78+455???
B141353-43252134???
C1351432-235424355???

So, all of the numbers are month over month differences.  These are correct.

The problem is, when we select 'Show Row Grand Totals' the results are blank.

Any way to display the SUM(Difference(field))?

Any help greatly appreciated.

Thanks,

Dan

• ###### 1. Re: How to create a row grand total for a Difference (Quick Calculation)?

Dan,

There are a few options to get the results you are looking, attached is one option. It does make use of a few interesting concepts of Tableau, using a nested table calc to detect if it is in the grand total, and then performing a different calculation.

• ###### 2. Re: How to create a row grand total for a Difference (Quick Calculation)?

Hi Joe

Trying to adapt it to a client's request I tried to understand the code of [Difference (with Total)].

Following the structure of the nested IF..THEN, I have the following remarks resp. questions:
a) Is the highlighted term not an unnecessary piece of code?. I think one could replace it by just LAST()as the programm cursor already confirmed FIRST()==0

b) Don't we need an ELSE statement for the inner IF..THEN or does the case of fullfilling [In Grand Total] AND NOT FIRST()==0 never happen ?

IF [In Grand Total] THEN

IF FIRST()==0 THEN

WINDOW_SUM(

SUM([UnrealizedPnL]) - LOOKUP(SUM([UnrealizedPnL]), -1),

0,

IIF(FIRST()==0,LAST(),0)

)

END

ELSE

SUM([UnrealizedPnL]) - LOOKUP(SUM([UnrealizedPnL]), -1)

END

• ###### 3. Re: How to create a row grand total for a Difference (Quick Calculation)?

Bernd this is a four year old thread, and the code in red was constructed by Richard Leeke as a workaround for a performance issue in the 6.x days. It kept T from cycling through every record. It is now completely unnecessary. I'm fairly sure you can get rid of both the start & end. Try this:

IF [In Grand Total] THEN

IF FIRST()==0 THEN

WINDOW_SUM(

SUM([UnrealizedPnL]) - LOOKUP(SUM([UnrealizedPnL]), -1)

)

END

ELSE

SUM([UnrealizedPnL]) - LOOKUP(SUM([UnrealizedPnL]), -1)

END

--Shawn