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

# Best method to count how many records in a bin

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

• ###### 1. Re: Best method to count how many records in a bin

Hi Steven,

Thanks

Deepak

• ###### 2. Re: Best method to count how many records in a bin

Every time I go to create the Bins Dimension, it changes it to a measure and will not let me move it back.  Is there a trick to move it back.  I assume its important that it be up in the Dimensions and not in Measures.

• ###### 3. Re: Best method to count how many records in a bin

Pl attach a workbook to help u.

• ###### 4. Re: Best method to count how many records in a bin

Here it is.

• ###### 5. Re: Best method to count how many records in a bin

Live Connection..