1 Reply Latest reply on Feb 14, 2013 6:06 PM by Dimitri.B

    How to use 'group by' and case clause like SQL Server?

    Sergio Araya

      Hello, I just started using Tableau seems to be a great tool but I'm stuck with a simple problem, how can I create a bar chart with different filters from the same table? Let me explain:

       

      The table is:

       

      Transactions
      ID (INT)
      CustomerID (VARCHAR(15))
      Amount (INT)
      Status (VARCHAR(1))
      TranCode (VARCHAR(1))

       

      I want to show the total of transactions and the total of deposits per customer, like the following script.

       

      select t.CustomerID

                , count(t.id) as [TotalTransaction]

                , sum(case when t.TranCode='D' then 1 else 0 end) as [TotalDeposits]

      from Transactions t

      group by t.CustomerID

       

      I attached an example, the charts are segmented one with totalTrans and another with deposits, but I want to consolidate it in the same.

       

      Any help will be appreciate. Thanks.

        • 1. Re: How to use 'group by' and case clause like SQL Server?
          Dimitri.B

          Sergio,

          I am sure this is possible (at least in most cases), but your workbook is not packaged, so the data is in a separate file on your computer somewhere and is not available. Can you save it as .twbx and re-post? This will package the data inside the workbook.

           

          Generally speaking, Tableau does GROUP BY by default, every time your put that dimension on a sheet.

          Counting instances is as easy as just dragging ID onto a sheet and selecting Aggregation - Count.

          Conditional sum can be accomplished via calculated field, so instead of

           

          sum(case when t.TranCode='D' then 1 else 0 end) as [TotalDeposits]

           

          you would have something like

           

          SUM(IIF([TranCode]=='D',1,0))