Product Tour
forums
Use these forums to ask questions and discuss Tableau.
Using aggregations to combine the same customer
I have data of customers buying our product. Each time they buy is another record. When I try to visualize the data it's assuming each record is unique - that they're distinct individuals. Instead I want to combine all the same customers together which I thought was an aggregation but can't seem to get it to work.
Here's an example:
Customer -- Date/Time Purchased -- Category -- Item -- Customer Zip Code -- Customer Sex
Jeff -- 1/1/08 12:22 -- Music -- Aerosmith -- 01760 -- M
Sam -- 1/2/08 15:34 -- Music -- Beach Boys -- 01760 -- M
Jeff -- 1/2/08 16:34 -- Music -- Matchbox Twenty -- 01760 -- M
I only want to count "Jeff" as one customer, but also be able to show he purchased twice (and use that info). When I try to do it it's counting "Jeff" as 2 customers. Also, can I use the same solution to do the same to other fields - like combining all the items that were Aerosmith together to count as one item and then look at the data that way?
Thanks!
Subscribe to the comments on "Using aggregations to combine the same customer"
Comments
This sounds like an issue with the concept of "level of detail". If you chart just Customer versus the auto-generated "Number of Records" measure, you should see one entry for each customer. (Then put Category on the Color field to go from "bars" to "stacked bars"!) If you also have something (either in Column/Rows or on LevelOfDetail) that differs for each customer (say, date/time purchased), then you're going to get results that are broken out by that as well, giving you multiple rows per customer.
So don't think of it as "combining" records, but rather look at what your lowest or most specific level of detail is - Tableau will aggregate as much as it can, in a sense.
I have the same question but I don't understand your response.
I want to graph how many customers have total purchases within a range. So on the X axis I show $1k, $2k, etc in total purchases. On the Y axis I want to show the number of customers who's total purchases fit within each bin.
The way its doing it now it counts each row as a unique customer to be counted individually. So my "total purchases" bins are way too small and my "number of customers" are way too big.
Hi Diego,
I think I've created an example that is similar to your situation. Using our Sample - Superstore Sales data source I've created a view that has the total sales binned by every $1K and the number of distinct customers whose purchase amount corresponds with each bin.
The workbook is attached. Below is what I did:
First I created bins from the Sales field (in your example it would be the total purchases). You can create bins from a measure by right-clicking the measure in in the Data window and selecting Create Bins. When you a create bins you define the size of each bin. In my example I've made each bin $1000. Binned fields are shown in the Dimensions area of the Data window with a histogram icon.
Next I created a Tableau Data Extract so I could get access to the Count (Distinct) aggregation. Because my data source is originally an Excel file, the Count (Distinct) aggregation is not supported. However, you can select Data > Extract to extract the data to a Firebird database, which supports count distinct.
Finally, I build the view by placing the binned Sales field on the columns shelf and then the Customer Name field on the Rows shelf. For the Customer Name field I chose Count (Distinct) as the aggregation (right-click the field on the shelf).
The result is a histogram showing the number of distinct customers who made a purchase for each amount. Does that get you what you are trying for?
I've got a similar requirement, but I think it has an additional level of complexity that requires a different approach. I've got an Excel database with employee data, with columns for employee ID, age, job classification and several other dimensions. As some employees work in more than one job classification, there are multiple rows for some of them. Also, even if an employee only works at one job classification, there may be multiple rows with the same values for employee ID, age and job classification because of varying values in the other dimensions.
I've created an extract file so that I can use the countd function. I then defined a calculated field Staff = Countd([employee ID]) and use this to count the number of unique employees by job classification. I now need to calculate the average age for these employees, by job classification. However, if I use a formula along the lines of SUM([Age])/Sum([Number of Records]) or SUM([Age])/[Staff], the results are incorrect. I really need a formula along the lines of "SUM([Age]) for just the records included in the countd processing'/([Staff]).
It should be possible to do this - at least, one could provide instructions to a person to manually process the file and get the correct results. This is feasible as each employee ID has the same value for Age in all rows for that employee. Can anyone suggest a way of accomplishing this in Tableau?