6 Replies Latest reply on Apr 2, 2013 11:36 AM by Ashley Howard

# Need help... totals and intermediate tables

Hey guys,

Need some help on this issue I have

Consider the data below:

http://img.photobucket.com/albums/v22/Alex_Chrono/Databases/IntermediaryTableIssue_zpsc7934560.png

Here are the tables:

http://img.photobucket.com/albums/v22/Alex_Chrono/Databases/IntermediaryTableDef_zps2f476aa9.png

I want to be able to have them displayed in Tableau (table form), somewhat like this:

http://img.photobucket.com/albums/v22/Alex_Chrono/Databases/IntermediaryTableResult_zps5fd1934a.png

The ACCT ID and Amount for "Bonnie" may or may not appear, what is important is the grand total only counts the amount for unique ACCT IDs (900) instead of all the records, currently when I do this in Tableau it shows "1000" (since it counts the amount for "Bonnie" as well).

• ###### 1. Re: Need help... totals and intermediate tables

Rommel -

Below is the step by step directions for achieving the attached.  Let me know if you have any trouble.

1) Place [AcctID] and [Name] on Rows self

2) Duplicate [AcctID], place on Level of Detail shelf

3) Create a new calculation:

IF MIN([Name]) != MAX([Name]) THEN

IF FIRST()==0 THEN

WINDOW_SUM(AVG([Amount]),0,IIF(FIRST()==0,LAST(),0))

END

ELSE

IF FIRST()==0 THEN

WINDOW_SUM(SUM([Amount]),0,IIF(FIRST()==0,LAST(),0))

END

END

4) Place New Calculation on Text Shelf

5) Set New Calculation to compete using your duplicate of AcctID

IF MIN([Name]) != MAX([Name]) THEN "..." sets the calculation for the grand total and the else "..." sets the row level calculation.  To understand what is going on I highly recommend reading Jonathan Drummey's series on Grand Totals which is where I learned the trick above:

• ###### 2. Re: Need help... totals and intermediate tables

Thank you very much Ashley, this worked great.

• ###### 3. Re: Need help... totals and intermediate tables

Additional question though... what if I want the value of "100" for "Bonnie" (or Joe) not appear in the report? How can that be done?

Thanks again!

• ###### 4. Re: Need help... totals and intermediate tables

Are you looking to include one row of names like Option 1 below.  I achieve this by creating a Rank Calculation - Index()=1, compute set to Name.  Placed on Filter shelf, select True.

OPTION 1

Or would you rather have both like this.  Take the same Rank Calculation.  Drop on color shelf.  Set false to white, true to black.

OPTION 2

• ###### 5. Re: Need help... totals and intermediate tables

Option 2.

What is the code to "not display" values?

• ###### 6. Re: Need help... totals and intermediate tables

Index()=1, set to compute using Name

True = Black

False = White