4 Replies Latest reply on Oct 20, 2016 8:59 PM by Mikey Michaels

    Create custom bins

    Mikey Michaels

      Hello All,

      I have been tasked with a project where I do not even know where to start!

      I need help creating the following:

      • Custom revenue bins 0-25K, 25K-50K,  50K-75K, 75K - 250K, >500K
      • In these buckets, I need to see where each Global DUNS customer's revenue falls
        • I need a formula to look at revenue from 2014 (2014 - License plus Hardware), 2015 (2015 - License plus Hardware), and 2016 (2016 - License plus Hardware) and determine over the last 3 years, where did the Global DUNS customer's largest revenue fall within the custom revenue bucket.
      • For example, Global DUNS 0032 had the following revenue:

       

       

      • Global DUNS numberGlobal DUNS name2014 - License plus Hardware2015 - License plus Hardware2016 - License plus Hardware
        0032NOLOGY60,879.89817.900.00

       

      Using the methodology explained above, this customer would fall into the 50K-75K bin. I would like the bins to count the number of Global Duns that falls into each.

       

      Whew, I hope I explain my issue well enough!

       

      As always, thank you so much for taking a look!

       

      Regards,

      Mikey

       

        • 1. Re: Create custom bins
          Srinidhi Narayan

          Apologies, as this may be a partial response to your problem.   Have you looked at creating custom bin sizes? Creating Variable Sized Bins | Tableau Software .   This will help you get the counts per bin.   However, to determine the sum (dollars) across the years and determining the count may need some thought.   If a DUNS fell in one bucket for one year, but another bucket for another year, how do you want to account for that?

          • 2. Re: Create custom bins
            Mikey Michaels

            Hi Srinidhi,

            Thank you for your reply!

            And thank you for the article -> this will definitely help me create the buckets.

             

            So I need to place each Global DUNS into only ONE bucket. I would need some sort of formula that would look at the 2014, 2015 and 2016 revenue individually, and place that Global DUNS into the bucket where the revenue was the highest over the 3 years. I would like to chart the count or number of customers that fall into each bucket

             

            For instance: Customer ABC

            2014     2015     2016  

            10K       150K        5K

             

            Since the revenue for customer ABC for 2015 was the highest @ 150K, I would put this customer into the 75K - 250K bucket.

             

            Thanks for taking a look!

            • 3. Re: Create custom bins
              Srinidhi Narayan

              Here is a potential solution.  Please note that this is based on how your data is structured.  Typically I would see this type of data pivoted by year and flattened out, but the solution proposed below will work with the way you currently have the data structured:

               

              1.  Create a calculation as follows -  call it  'Max Value':

               

              iif([2014 - License plus Hardware] > iif([2016 - License plus Hardware] > [2015 - License plus Hardware], [2016 - License plus Hardware], [2015 - License plus Hardware]), [2014 - License plus Hardware], iif([2016 - License plus Hardware] > [2015 - License plus Hardware], [2016 - License plus Hardware], [2015 - License plus Hardware]))

               

              2. Next create another calc - call it "Value Bins"

               

              IF (([Max Value] <= 25000) and [Max Value] > 0) then '0-25k'
              ELSEIF (([Max Value] <= 50000) and [Max Value] > 25000) then '25-50k'
              ELSEIF (([Max Value] <= 75000) and [Max Value] > 50000) then '50-75k'
              ELSEIF (([Max Value] <= 250000) and [Max Value] > 75000) then '75-250k'
              ELSEIF   (([Max Value] <= 500000) and [Max Value] > 250000) then '250-500k'
              ELSE '>500k'
              END

               

              3.  Now, create a new viz and drop "Value Bins" on Columns, and the "Number of Records" pill onto Rows.  You should get a bar chart viz that looks like this:

               

              Sheet 3.png

               

              Let me know if that is what you wanted.

               

              I have the workbook attached, but it is saved in a later version (10.0) than your original, so not sure if you can open it.  Hence the instructions. 

              • 4. Re: Create custom bins
                Mikey Michaels

                This is excellent...thank you, Srinidhi!