5 Replies Latest reply on Jun 15, 2018 3:56 PM by Deepak Rai

    Best method to count how many records in a bin

    Steven Mikolasek

      I'm creating a worksheet that needs to calculate the difference between two dates:

      DATEDIFF("day",MAX([Employee Hired Date]),MAX([Date Terminated]))

       

      Has 7 Bins:

      IF [Tenure]<181 THEN "Less Than 6 Months"

      ELSEIF [Tenure]>=182 AND [Tenure]<=365 THEN "6 Months to 1 Year"

      ELSEIF [Tenure]>=366 AND [Tenure]<=1095 THEN "1 to 3 Years"

      ELSEIF [Tenure]>=1096 AND [Tenure]<=1825 THEN "3 to 5 Years"

      ELSEIF [Tenure]>=1826 AND [Tenure]<=3650 THEN "5 to 10 Years"

      ELSEIF [Tenure]>=3651 AND [Tenure]<=7300 THEN "10 to 20 Years"

      ELSE "20 Years or More"

      END

       

      The worksheet is currently calculating the difference correctly and listing the difference under the correct bins:

      But I'm needing to count how many are in each bin.  I'm struggling with how to go about this and the various blogs give numerous different ways to accomplish this and I haven't found one that works, assuming I've got it coded correctly, so I could use some help.  Bins are across the top and employees are down the left side.

       

      AGG(Tenure) with a number data type

      AGG(Bins) with a string data type