1 2 Previous Next 20 Replies Latest reply on Mar 28, 2011 12:56 PM by Ian Waring

    Average # Unique Part Nos sold to a customer?

    Ian Waring

      We have a standard dashboard that shows the key business dimensions of any profitable business; to make more profit, you sell more products, to more people, more often and at higher margin (increased sell price and/or lower cost). One of the graphs I generate to this end is the average number of unique part numbers customers buy, so we can measure our success over time of customers buying a increasing range of products from us.

       

      I have an extract that contains a list of sales transactions, including customer id (number), part number ordered and an invoice date. I'm trying to work out the average number of unique part numbers sold to each buying customer in each year.

       

      I've originally done #unique parts divided by #unique customer ids, but this is clearly wrong; if I did it properly, I should never see less than one unique part number sold a buying customer on average. That said, I can't work out how to get Tableau to spit out the correct aggregation to show the correct average. Any ideas that could get my on my way?

        • 1. Re: Average # Unique Part Nos sold to a customer?
          Richard Leeke

          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.  ;-)

          • 2. Re: Average # Unique Part Nos sold to a customer?
            Ian Waring

            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...

            • 3. Re: Average # Unique Part Nos sold to a customer?
              Joe Mako

              Ian,

               

              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?

              • 4. Re: Average # Unique Part Nos sold to a customer?
                Richard Leeke

                Ian

                 

                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

                    WINDOW_AVG(COUNTD([Part_ID]))

                END

                 

                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.

                • 5. Re: Average # Unique Part Nos sold to a customer?
                  Ian Waring

                  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.

                  • 6. Re: Average # Unique Part Nos sold to a customer?
                    Ian Waring

                    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?

                    • 7. Re: Average # Unique Part Nos sold to a customer?
                      Joe Mako

                      You can use the attached image to see how to get to the formatting option to hide marks when the value is null (special value).

                      • 8. Re: Average # Unique Part Nos sold to a customer?
                        Richard Leeke

                        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.

                        • 9. Re: Average # Unique Part Nos sold to a customer?
                          Ian Waring

                          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.

                          • 10. Re: Average # Unique Part Nos sold to a customer?
                            Richard Leeke

                            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.

                            • 11. Re: Average # Unique Part Nos sold to a customer?
                              Ian Waring

                              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.

                               

                              Ian W.

                              • 12. Re: Average # Unique Part Nos sold to a customer?
                                Joe Mako

                                I attached what I am guessing you are looking for.

                                 

                                If it is, I can go into more detail.

                                • 13. Re: Average # Unique Part Nos sold to a customer?
                                  Ian Waring

                                  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!

                                  1 2 Previous Next