3 Replies Latest reply on Feb 4, 2013 8:57 AM by Michael Holt

    Average Number of Transactions Over Time

    Michael Holt

      I'm having trouble doing what I feel should be a simple task in Tableau...perhaps I'm just missing a huge step in the process. Nevertheless, here's my situation:


      We are a large non-profit performing arts organization and we sell tickets to events and performances. I'm connected to a SQL database that has recorded every transaction of my company since 2006. Each row (over 500,000) contains the following information: Client_ID, First_Name, Last_Name, Address, City, State, Zip_Code, Event_Series, Event_Name, Event_Date, Event_Time, Transaction_Date, Transaction_Amount, Quantity_Tickets. 


      What we want to understand is the average number of transactions per person over time. For example, in 2010 on average, customers made 2 sales transactions. What I have tried to do so far is in the row column I have placed SUM(Transaction_Amount) and then used the Number of Records calculated value to find the total number of transactions that took place. I then take the Sum(Transaction_Amount) out of the row. In the column area I put Client_ID, which then shows the number of transactions per Client_ID (so I'm able to see that Client #123546 has made 34 purchases since 2006. I also place the Event_Date field in column area so I'm able to break it down by year. However, I'm stuck on the next step to make it possible to see the average amount of transactions per year. 


      We want to arrive at an average transaction per year, so we can decide what constitutes "normal" attendance at our center. Median might be a better measure, since there are quite a few outliers who attend substantially more than others.


      I'm sure there's a way to achieve what we're looking for with a calculated field, but I need help to get there.