1 Reply Latest reply on May 27, 2015 9:11 PM by Ying Sun

    Trying to create a running total line chart using the attached data

    Daniel Gerena

      I have detailed transactional records for a few hundred bank branches. I want to create a chart that shows a running total of unique branches based on the MM/YYYY each branch has its FIRST transaction, so i can show the adoption rate of branches over time, like the chart below.

       

      Here's the data...so we want to only chart the first record for each branch (it's sorted by date thus the bolded records only), then make a chart that puts a "1" on each record, then charts it by the Date (in MM/YYYY format) using a running sum function.

      Branch Date Amount

      A 1/1/2014 2

      A 1/16/2014 1

      A 2/5/2015 3

      B 2/7/2014 3

      B 3/1/2014 1

      C 4/7/2014 2

      C 5/5/2014 4

      D 1/8/2014 4

      D 1/17/2014 3

      D 1/22/2014 3

      E 3/1/2014 5

      E 4/1/2014 1

      E 4/3/2014 1

      F 1/7/2014 5

      F 1/31/2014 2

       

       

      Running total.bmp

        • 1. Re: Trying to create a running total line chart using the attached data
          Ying Sun

          Hi Daniel,

           

          You can clean up the raw data by using custom SQL (group by branch and min(date)) and you will only bring bolded records to tableau.

           

          or you can create calculated field in tableau.

          calculation 1:  { FIXED [Branch]: DATE(MIN([Date]))} to give you FIRST transaction date for each branch. move it to columns shelf

          Calculation 2: { FIXED [Branch],[Calculation1]: COUNTD([Branch])} to give you number of unique branch. move it to Rows shelf and add quick tableau calculation as running total.