8 Replies Latest reply on Mar 2, 2012 3:03 PM by Charles Bertram

    Binning or calc field to sort data by size?

    Charles Bertram

      I have a set of data in Excel which shows sales deals won or lost, reason for win or loss and $ value. Some of the deals are broken into component parts, so I am using COUNTD to get a count of the deals.  I now need to break the data set up by total $ value (<$25K, $25K - $100K and $100K+).

       

      I have looked at binning the data, but that only seems to work if your bins are equal size, which leaves me with a calc field.

       

      I tried using IF ([Amount]) < 25000 then [Unique ID] end (and then using COUNTD with the result), but I am not convinced that what that gives me is right. It won't let me SUM the amount because it is them an aggregate.

       

      Any ideas?  I will see if I can post a sample data set or a screen shot of what I have now.

        • 1. Re: Binning or calc field to sort data by size?
          Jonathan Drummey

          You're on the right track with using an IF statement in place of Tableau's bin function.  However, from your description, I'm not sure what the total $ value is referring to, and where it can be found. Is it made up of the sum of the component parts for each deal, is it duplicated within the deal, or is it another aggregation? Having some sample data would be useful to be able to give you more guidance.

           

          Jonathan

          • 2. Re: Binning or calc field to sort data by size?
            Charles Bertram

            Thanks Jonathan,

             

            I have attached a file with some sample data and also a screen shot of the viz I am working on. The total $ value is coming from the sum of the Amount field, which in some cases is a single row of data and in others can be as many as 20 or 30 rows. The opportunity number is unique ID, but I don't know if that is ho you get around the fact that Tableau wants to aggregate the amount data together.

             

            Thanks for any guidance you can provide.

            Charles

            • 3. Re: Binning or calc field to sort data by size?
              Jonathan Drummey

              This was fun. Here was my process (also shown in the attached workbook):

               

              1. Drag Market Segment and Win/Loss Reason to the Rows shelf.

               

              2. Create the bins with the following statement (I put it in a calculated field called "Amount Bin"):

               

              IF SUM([Amount]) < 25000 THEN "<$25K"

              ELSEIF SUM([Amount]) < 100000 THEN "<$100K"

              ELSE "$100K+"

              END

               

              Now, the question is what will the SUM be over? We know it needs to be on each Opty Nbr, so we put that on the Level of Detail shelf. This will cause duplicate marks for the moment, we'll clean those up later.

               

              2. Put the Amount Bin on the Columns shelf.

               

              3. The secret sauce: Click on the Amount Bin pill on the Columns shelf and unclick "Ignore in Table Calculations". By default, Tableau treats discrete fields as dimensions, however when those dimensions made of calculated fields it ignores them in table calculations. We need the Amount Bin for the next step, so we change the setting.

               

              4. Create another calculated field (I put it in a calculated field called Window Count of Opty Nbr):

               

              IF FIRST() ==0 THEN

                  WINDOW_COUNT([Amount Bin], 0, IIF(FIRST()==0, LAST(), 0))

              END

               

              The key bit here is the WINDOW_COUNT([Amount Bin]). Effectively, we're using the calculated bin as a way to count how many opportunities fall into each bin. Tableau's defaults will make an accurate calculation, as far as I can tell. The rest of the statement is to prevent overlapping text, drawn from Richard Leeke's work in this post: http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html.

               

              5. Drag the Window Count of Opty Nbr onto the Columns shelf.

               

              Does this meet your needs?

               

              Jonthan

              • 4. Re: Binning or calc field to sort data by size?
                Charles Bertram

                Hi Jonathan

                 

                Getting very close! I saw that you also added the Opty Nbr pill to Level of Detail. I get why we need that, but it means that I can't show the total number of opportunities under each Win/Loss Reason because the viz is essentially showing each opportunity as a tiny colored line. I played around with it, but couldn't work out how to show the total # of opty nbrs under each bin.

                 

                Also, should I rename the <$100K bin to $25K - $100K?

                 

                thanks

                Charles

                • 5. Re: Binning or calc field to sort data by size?
                  Jonathan Drummey

                  If you control-drag the Window Count of Opty Nbr pill to the Label shelf, the # of opportunities will be displayed on the bar chart. That copies the exact settings of the table calculation so they stay the same on both the Columns shelf and the Label shelf.

                   

                  You can play with the formatting of the bars and font sizes to make things work for you there. And yes, the <$100K bin should probably get renamed.

                   

                  In terms of coloring the bars, you should be able to put most anything on the Color shelf and the calculation will accomodate that, if not you'd need to work with the Compute settings for the table calculation.

                   

                  Jonathan

                  • 6. Re: Binning or calc field to sort data by size?
                    Charles Bertram

                    I'm not getting the same results as you and the only reason I can think of is because the sample data set I sent you is a fraction of what I actually have.

                     

                    Because this is driving me crazy and I've just been told that I need to present this to leadership on Monday, I have attached my full data set. Not quite sure why I didn't send this to you in the first place! The report I am working on links the two tabs together using Tables, but I'm not sure that is the best way to do it and may be what is causing me problems.

                     

                    I appreciate you working on this with me!

                    Charles

                    • 7. Re: Binning or calc field to sort data by size?
                      Jonathan Drummey

                      Here you go. I tried out a blend, but ran into issues with getting the labels right (because Opty Nbr had to be in the Level of Detail, lots of 1's were showing up), so went back to using a join. Here's a workbook where the view looks much like what you'd posted as a screenshot in the initial sample data.

                       

                      The only fields that are in the By Region worksheet that are not in the WinLossQ3 worksheet are SumofAmount and WinLossReason, it seems like both of these could be derived either in the SQL connection or as a calculation in Tableau, if that would make things simpler for you.

                       

                      Jonathan

                      • 8. Re: Binning or calc field to sort data by size?
                        Charles Bertram

                        It works!! You are a star - thank you so much for your help and just in time for my presentation on Monday.

                         

                        You also led me to some issues with my underlying data which is why I was not getting a perfect match with my counts from one dashboard viz to another.

                         

                        thanks again!