4 Replies Latest reply on Apr 16, 2013 3:55 AM by David Baudrez

    Sales $ Bands/Bin calculations - issues at aggregated level

    David Baudrez

      Dear Tableau friends;

       

      Find a few questions my team and I have been struggling with for 2 weeks. Maybe some of you faced similar issues and could give us some hints.

      When trivial in Excell/Access, we are banging our heads to solve it within Tableau... OK we are not yet experts, reason why we are looking for your support. Appreciated.

       

      Based on Super Store Sales example; we are struggling to perform a couple of queries

       

      1. Identify and Classify Products transactions by bin-size - what we call "sales $ bands".
        • Classify products with a sales of 0-2500$; 2501-5000$, 5001-10000$,...
        • In a particular Year, Quarter or Month
        • For a particular time period (over multiple month / quarter / years
        • => seems ok with the creation of a calculated field: (bookings band - simple) see tab 1&2 in example attached
      2. Use this "Sales $ bands" concept at aggregated level => by customer; by category or sub category, by region
        • => Seems working by the creation of a calculated field (booking bands - sum) see tab 3 of example
      3. Counting and Classifying # of customers by Sales $ Band: that is where the struggle begin
        • Instead of seeing 2 transactions in a particular Sales$ Band we  would see the total for that customer and then apply the bin formula.
        • Then we want to count the number of customers in each sales$band
        • =>Seems the (Booking bands - sum) cannot be used as a Dimension but only as a measure (as aggregated)
        • =>Seems the Count Distinct function is not showing. that could have help in 4th tab to differentiate by transaction or customer level
        • =>We have been trying to link the Sales measure with the customer dimension but without success
      4. Finally, we would like to apply similar concept but looking at Frequency Bands: classifying customers by # of transactions they have done in a particular period of time : 0-2; 3-5; 5-10; >10 and use that as another dimension to analyse our customer base.

       

      We received the hint that the solutions should exist by applying advanced table calculations to the dataset. But we are not skilled enough to do that by ourselves. We are about to hire an expert but it gonna take another few weeks. In the meanwhile; we would be very keen to get some feedback from the community.

      Many thanks

       

      David Baudrez

      dbaudrez@cisco.com

        • 1. Re: Sales $ Bands/Bin calculations - issues at aggregated level
          Robin Kennedy

          David,

           

          You're right that some table calculations are required. you also need to make the COUNTD (count distinct) function available by switching to an extract -- this function is not supported by MS Excel. You also need to un-tick the 'Ignore in table calculations' option on the Booking bands field.

           

          Please take a look at tab 4 of the workbook I have uploaded, does this achieve what you need?

           

          Best of luck,

          Robin.

          1 of 1 people found this helpful
          • 2. Re: Sales $ Bands/Bin calculations - issues at aggregated level
            David Baudrez

            Hi Robin,

            Thanks for your feedback and indeed; your example is helpful to progress;

            Having said that doesnt solve our issue as there are some calculation mistakes with this formula

             

            From the sheet you sent back: if you take the 25-50K line in 2011 => value is 10

            Looking at the underlying data we see:

            - only 1 customer Cindy Schnelling with 10 in distinct customer count

            Deep diving one level down:

            only 7 lines; only from 1 customer; for a total of 27K

             

            What I was expecting to see is the count distinct being 1 instead of 10: Only 1 customer in that band in 2011. With 10 transaction for a sum of 27K.

             

            Any suggestion?

             

            I got a couple of advanced users that tells me to do the consolidation by bin and flagging outside of Tableau in order to workaround. But this would be definitly a sub-optimal solution for us as we are really looking at building those booking bands in a dynamic way...

            For example; I would be able to do the math and show the demography by booking bands for the entire business and for all the regions. Then play with regional or category filters to recalculate my demography at the fingertip. I wish you are following me :-).

             

            Thanks for your other feedback/hints

             

             

            .

            • 3. Re: Sales $ Bands/Bin calculations - issues at aggregated level
              Robin Kennedy

              HI David,

               

              The 10 figure for 2011 sales 25k-50k is correct -- check it out on a separate sheet by putting Customer on Rows, Sum(sales) on text and Year(order date) = 2011 on filters

               

              The reason you were getting only 1 customer up when looking at the data is because of the way the data is being displayed as part of the table calculations -- each customer is being calculated individually and totaled individually -- i.e. there are 11 11's, and picking the top one, just gives one instance of the 11!

              • 4. Re: Sales $ Bands/Bin calculations - issues at aggregated level
                David Baudrez

                Thanks Robin, it seems very close to what we were looking for.

                I am deepdiving a bit further and may come back to you with a few more sub-questions.

                Big thanks again!

                 

                Attached the VIZ created based on my original file and request.