Put [customer_id] on your view (maybe on LoD). Create a calculated field of WINDOW_AVG(COUNTD[part_id]). That should do it - I think - it's always hard to imagine without trying it. ;-)
Thank you Richard.
I've done this and now have a list that goes
CustomerRef 2009 2010 2011
CustomerIDNo 3.00 4.50 4.25
CustomerIDNo 2.00 3.70 5.65
CustomerIDNo 4.60 4.20 4.35
CustomerIDNo 18.00 19.50 17.85
CustomerIDNo 3.00 4.50 4.25
etc - but can't work out how to aggregrate the average of these for all accounts in a specific year (so we can see how the overall number of unique products per customer is trending). If I remove CustomerID number from the shelf, I get much larger numbers that what, by inspection, the average of each column should be.
Sorry if this is Tableau 101...
With the sample aggregated data that you are provided, I am unable to help you create the analysis you are looking for.
Can you please mock up some sample data that represents your source data situation, and maybe create a packaged workbook of what you have so far?
I'll just try to describe briefly what you need to do - but as per Joe's comment, these things are much easier to explain with some sample data. If I find a few minutes I might try to put an example together with some made up data - but it would save me a bit of time and make sure I was actually answering the right question if you could put a dummy workbook together.
The reason that you need [customer_id] on one of the shelves is that that is the level at which you want the distinct count of part #s. In fact you need customer and year, so you get the count of unique parts per customer, per year.
But you don't want to display the count per customer, you just want the yearly average over all customers. That's where the WINDOW_AVG() comes in. So you need to define the calculated field as WINDOW_AVG(COUNTD([part_id])) - and then make sure that the window calculation calculates the average over all customers for each year. You do that by defining the partitioning - right click the pill for the calculated field and select edit table calculation. In the Table Calculation dialog select Compute Using [Customer_ID]. That should now display a message under Description that says something like "Results are computed along Customer_ID for Year of Date.
So now the calculated field should be giving the same value for all customers - the overall average you are after. Each year should be different.
But you don't want to see it once per customer, you just want to see it once. So you can move [Customer_ID] to Level of Detail, which is almost what you want - but it will actually be displaying the same answer multiple times - once per customer - but just all drawn on top of each other.
You can either accept that as good enough - or for extra credit (as the saying goes), you can suppress all but one of them. All you need to do is alter the table calculation so it only calculates an answer for one row. You can choose which row arbitrarily, but typically I just use the first row, like this:
IF FIRST()==0 THEN
That means the first row will have the right answer and all other rows will be null. Finally, if you put a copy of the resulting calculation on the filter shelf and choose special - non-null values, you should have exactly what you want.
Does that help? It does take a while to get your head round table calculations.
That's *brilliant*. One day the day job will not be so time pressured for me to learn these things!
Thankyou very much indeed - it is greatly appreciated.
All done, working, but only one gotcha. If I hit the "Show Me!" button and do a line plot, I get the trend of the averages perfectly - but I get three data points and another line joining them all together on the x-axis too (see the attached PDF). Is there any way of supressing that rogue ( guess first Customer ID) on that axis line?
The other option may be just to filter out the NULL values as I mentioned above. Did you try that? I generally like to get rid of all the NULL marks from the final view if I can, they can cause confusion in other ways, too.
> That means the first row will have the right answer and all other rows will be null. Finally, if you put a copy of the resulting
> calculation on the filter shelf and choose special - non-null values, you should have exactly what you want.
Sorry Joe, Richard,
You're both correct. Add the non-null filter at it graphed okay for one of my vendors. However, I have something strange happening - when displayed as a line graph, if I change the vendor filter, I get colored dots which reflect averages associated with different customer IDs. Think I have some Customer IDs which are nulls, so need a play around to see what's going wrong. More later.
You might need to put something on the Path shelf - sometimes having the fields you need on Level of Detail in order for you table calcs to work can mean that Tableau needs a bit of help joining up the dots - it can't always correctly infer the series for the lines.
Right, sample sheet and sample data. Open the workbook, import the data from the spreadsheet, making an extract (needed for the COUNTDs).
If you select all the PGMJs starting with an "H", you get a nice graph of 2009, 2010 and 2011 average # unique stock codes sold per customer.
If you then select PGMJs with a "None", then select "NWI" and "NP", the graph becomes colored dots.
Separate selections of "EMC", EPV", "LEG" and "DDM" are another meaningful subset.
As is "HDS".
Any ideas what i'm doing wrong? I wanted a line graph on my dashboard independent of which PGMJs (Product Group Majors) were selected.
It is indeed Joe - brilliant - though I can't replicate it's exact behaviour with the INDEX(). I seem to be getting the dots and no line on my live data. But any explanation would help me. Thanks very much indeed for an impressive result - I just need to know the nuances!