9 Replies Latest reply on Feb 8, 2012 3:49 PM by . Marellano

    How to have a summary report use an aggregated Quick Filter at different level of data

    . Marellano

      Hello Tableau Forum,

       

      I'm having problems with Table Calculations.

       

      The attached Tableau workbook contains a Summary report of Members with multiple accounts types (Shares, Investments and Loans).  The data is summarzed at the Branch level.

       

      I'd like to create a quick filter that aggregates the total $ amount of Shares at the Member level.  In other words, each member can have more than one Share Account and the aggregated totals will determine if the member is included/excluded.

       

      I've tried using the WINDOW_SUM function with the results being computed at the Member Level.  When this didn't work, i tried the calculation at all different levels and could not get the Quick Filter to aggregate properly.

       

      The following is what I've tried so far:

       

      1. Created a Calculation that takes the balances for all Share ccounts types:  [Bal S]

       

      IIF([L_ACCT_TYPE]='S',[CURRENT_BALANCE],0)

       

      2. Created a second calculation using [Bal S] and WINDOW_SUM: [Member S Agg Bal]

           The "Default Table Caculation is set at the Member level:

       

      WINDOW_SUM(SUM([Bal S]))   

       

      3.  Added the table calculation , [Member S Agg Bal], as a Quick Filter and set the "compute using" at different cell, field and table options.

       

      So in the following example,  if want to filter the Summary report on all members that have an aggregate balance greater than 250K, this member should show up:

       

       

      CU_GRPL_ACCT_LEVELL_ACCT_TYPEMEM_NUMCURRENT_BALANCE
      V11S29866$287,167
      V10S29866$342
      V1S29866$326
      TOTAL SHARES$287,835

       

      Thanks for your assistance in advance,

       

      marellano