HeadCount year by year barchart

[Horit]

I have data set like employee_id , hire_date, termination_date , also I have a master calendar . I want create a bar chart which shows yearly active head count. There is a filter to select the years to be shown on the bar chart . Each year should be a fiscal year starting from 1-Aug-Year to 31-Jul-(Year+1). Can anyone please suggest any way to do this 

[Mark_Little]

Hi @Horit 

This is going to depend a lot on your data model and conditions for your head count. 

I would look at the Interval match function to find the when people are employed. i.e. Between hire and termination.

[Horit]

thanks for the reply ... I will elaborate a bit more 

please find the attached data sheet .
for the time being If we do not consider Fiscal year .

suppose there are two variables - start date and end date  ( based on this range bar graph will show the chart yearly basis ) 

if I select start date = 1 jan 2020 , end date = 31 jul 2022 then the bar graph should visualize 3 bars 

saying - 2022 - 155(head count)

                 2021 - 140

                  2020 -110

explain - for headcount in 2022 - all the terminations in 2022 will be excluded , all the hire in 2022 will be included ..

Can u please suggest a way .... or any solution .. 

there are two sheets in the excel sheet .... 1. HRMS sample , 2. Calendar 

do we need to join the two tables ?

Sorry , I can't share the workbook for some info sec reason...

Regards 

Horit

[eticam]

Bar charts display values as individual bars whose height is determined by the value plotted and grouped by each category. This is best used to compare data ...https://paystublogin.onl/nyc-doe-payroll-portal/ 

[Horit]

HI @Mark_Little  , please suggest or share any solution for the case mentioned below