Complex age binning help required

[Anonymous]

 Hi,

Wondering if the community could help solve a way to do this.

I have a requirement to show a vis that will bin a count of staff into age groups (<16, 16-25, 25-35, 35-45, 45-55, 55-65, >65). Using normal binning technique I would apply a calculated column that would calculate the bin based on age and then plot count of staff against the bin attribute.

The requirements that are making this one a bit trickier is that I have to show a count of staff employed this year against a count of staff employed during the same period last year. This will mean that staff who are employed this year will be a year younger last year and they may fall into a different bin. I can't use the normal method of creating a calculated column as this is only calculated on load so the normal bin category would be fixed. I can't create a second column for the previous year bin as I need to show both counts on the same vis.

I've included some exemplar data below to assist if anyone can help with this.

Employee
StaffID	Name	DOB
1	Elnora Harland	22/8/1965
2	Talbot Spence	4/2/1991
3	Norene Johnson	10/5/1984
4	Parris Wolfe	25/7/1954
5	Joanna Honeysett	16/9/1993
6	Nik Tollemache	16/10/1985
7	Zeke Thorne	13/1/1974

EmploymentHistory
StaffID	RoleID	DateStart	DateEnd
1	9001	10/01/2002	15/06/2009
1	9002	16/06/2009	2/12/2015
1	9003	3/12/2015
2	9001	16/06/2009	12/04/2018
3	9001	15/12/2018
4	9005	12/06/2016	12/08/2016
4	9006	13/08/2016	13/11/2016
4	9005	14/11/2016
5	9006	02/07/2010	11/08/2016
5	9006	14/11/2016
6	9007	01/02/2015	31/05/2018
7	9008	24/01/2018	31/07/2018

Anyone have an idea on how to do this?

[v-yulgu-msft]

Hi @Anonymous,

It's not very clear about the requirement.

Does age refers to the age of a staff which is the datediff between current year and DOB, or refers to the time a staff has been employed unitll now which is the datediff between DateStart and DateEnd?

Can you illustarte "This will mean that staff who are employed this year will be a year younger last year and they may fall into a different bin." with examples?

What is the desired result based on above sample data? Please provide more detailed description.

Regards,

Yuliana Gu

[Anonymous]

Thanks for the reply @v-yulgu-msft.

Age is DATEDIF from today.

If I were to work this out manually into a table I'd get the following:

StaffID	Name	DOB	Age Current	Age Last Year	Bin This Year	Bin Last Year	Employed This Year	Employed Last Year
1	Elnora Harland	22/08/1965	53	52	45-55	45-55	1	1
2	Talbot Spence	04/02/1991	28	27	25-35	25-35	0	1
3	Norene Johnson	10/05/1984	34	33	25-35	25-35	1	0
4	Parris Wolfe	25/07/1954	64	63	55-65	55-65	1	1
5	Joanna Honeysett	16/09/1993	25	24	25-35	15-25	1	1
6	Nik Tollemache	16/10/1985	33	32	25-35	25-35	0	1
7	Zeke Thorne	13/01/1974	45	44	45-55	35-45	0	1

You can see how StaffID 5 and 7 fall into a different age bin when we count at the same time last year.

Also you'll notice that StaffID 3 is onlly counted this year as they weren't employed last year and StaffIDs 2, 6 and 7 aren't counted this year as they are no longer employed but were last year.

Following that through I should be able to get an answer table as shown below.

Bin	This Year	Last Year
<15	0	0
15-25	0	1
25-35	2	2
35-45	0	1
45-55	1	1
55-65	1	1
>65	0	0

Hope this helps explain things a bit furhter.