2 Replies Latest reply on Jan 20, 2017 7:07 AM by Herman Li

    In crosstab, how to turn subtotal columns into "closing balance" columns?

    Herman Li

      Hi Tableau experts,

       

      I am pretty new to Tableau, so my question may seem silly to all of you but I got stuck in Tableau. Here's my problem overview:

       

      Environment: Tableau Desktop + Server (v 10.1)

      Datasource:   MS SQL Server, extract

      CrossTab Layout:  3 Row Dimensions

                                    2 Column Dimensions

                                    2 Measures (placed in rows)

       

       

      Instead of demonstrating the problem with my huge and production datasource, a much simpler packaged workbook with dummy data is attached for illustration.

      The raw data looks like this:

      DateTransTypeUSDHKD
      2016/3/15IN45.10349.53
      2016/3/15OUT-0.40-3.10
      2016/4/15IN91.60709.90
      2016/4/15OUT-36.80-285.20
      2016/5/15IN27.10210.03
      2016/5/15OUT-98.80-765.70
      2016/6/15IN51.80401.45
      2016/6/15OUT-52.40-406.10
      2016/7/15IN76.80595.20
      2016/7/15OUT-74.10-574.28
      2016/8/15IN34.90270.48
      2016/8/15OUT-99.80-773.45
      2016/9/15IN0.403.10
      2016/9/15OUT-73.00-565.75
      2016/10/15IN4.0031.00
      2016/10/15OUT-54.20-420.05
      2016/11/15IN35.40274.35
      2016/11/15OUT-78.00-604.50
      2016/12/15IN76.60593.65
      2016/12/15OUT-66.50-515.38
      2017/1/15IN42.60330.15
      2017/1/15OUT-99.40-770.35
      2017/2/15IN99.60771.90
      2017/2/15OUT-12.40-96.10
      2017/3/15IN3.6027.90
      2017/3/15OUT-28.50-220.88
      2017/4/15IN48.40375.10
      2017/4/15OUT-17.90-138.73

       

      And the crosstab looks like:

      The "Total" columns were generated from standard Tableau function "Add all subtotals".

       

      I don't know if any calculated fields / table calculated fields / sets etc. can achieve what I want:

      "Closing Balance of month(X) = Opening Balance of month(X-1) + IN of month(X) + OUT of month(X)" for EACH MEASURE,      where Opening Balances of first month are all zeros

       

      Running_Sum() seems to be the right approach to my problem, but Tableau doesn't allow me the configure the behavior of subtotals as Running_Sum().

       

      Very much appreciated if anyone can guide me through. Thanks

       

      Herman