7 Replies Latest reply on Oct 15, 2019 2:01 PM by Don Wise

    Counting Buckets

    Alex Dowd

      Hello,

       

      I am aiming to count number of people by tenure. Specifically, the number of people who fall within 0-3 years, 4-6 years, 7-10 years and 10+ years. Can anyone assist with the calculations here? Thank you!

        • 1. Re: Counting Buckets
          Deepak Rai

          Please attach a workbook

          • 2. Re: Counting Buckets
            Budi Lubis

            Hi Alex -

            There are several ways to address your questions (i.e. bin). If you could give us a sample data and what you are trying to achieve or model, even better.

            • 3. Re: Counting Buckets
              Alex Dowd

              The information I'm using is CCI so I cannot share a workbook, however attached is a short excel document of the same type of data organized in the same way. My current method is to use a Histogram, however I'd like to be able to use calculated fields to just have a regular bar chart, or some other viz.

               

              Capture.PNG

              • 4. Re: Counting Buckets
                Don Wise

                Hi Alex,

                Try the following LOD calculation:

                 

                Buckets

                {FIXED [Employee Number]:  IF SUM([Tenure (Years)])>0 AND SUM([Tenure (Years)])<=3 THEN '0 -3 Years'

                ELSEIF SUM([Tenure (Years)])>3 AND SUM([Tenure (Years)])<=6 THEN '4 -6 Years'

                ELSEIF SUM([Tenure (Years)])>6 AND SUM([Tenure (Years)])<=10 THEN '7 -10 Years'

                ELSE '10+ Years' END}

                 

                You should get this:

                Screen Shot 2019-10-14 at 8.05.08 AM.png

                It focuses on the Employee Number and then buckets each employee number into the matching ranges.

                 

                If this answers your question please mark it as correct so that others may find it in their search for a solution in the future.  Best, Don  How to Mark an answer as Correct

                 

                We would love to hear your feedback on your Tableau Community Forums experience.

                Please take this 5 minute survey.

                 

                     Tableau Community Forums | Customer Experience Survey

                • 5. Re: Counting Buckets
                  Alex Dowd

                  Thank you!

                  • 6. Re: Counting Buckets
                    Alex Dowd

                    Don, so this worked mostly, however it's not working on all rows and I cannot figure out why. Any ideas?


                    For example - an employee with a tenure count of 0 is showing 0-3 years (correctly), but 4 is showing 10+ years.

                    • 7. Re: Counting Buckets
                      Don Wise

                      Hi Alex,

                      Not sure...I only had the small amount of flat data to work with so the mileage varies with actual results due to actual data volume, data structure, different systems, etc., can be a struggle !

                       

                      The only thing I can think of is that when using a Level of Detail calculation like FIXED, if there are additional Dimensions in the worksheet that weren't are part of the provided data, then generally those need to be included in the calculation as well.  For example, if you were also bringing in Employee Name or some other criteria then you'd want to put that field in with [Employee Number] ahead of the colon. 

                       

                      So it would look something like:

                       

                      Buckets

                      {FIXED [Employee Number], [Employee Name]:  IF SUM([Tenure (Years)])>0 AND SUM([Tenure (Years)])<=3 THEN '0 -3 Years'

                      ELSEIF SUM([Tenure (Years)])>3 AND SUM([Tenure (Years)])<=6 THEN '4 -6 Years'

                      ELSEIF SUM([Tenure (Years)])>6 AND SUM([Tenure (Years)])<=10 THEN '7 -10 Years'

                      ELSE '10+ Years' END}

                       

                      Best, Don