3 Replies Latest reply on Jul 27, 2017 10:27 AM by Rupali jain

Dear All,

I am finding it difficult to show the employee headcount numbers as on any quarter end. Basically whenever I pull the Year & Quarter in the column self and then the count of employee id in the row self it creates the view for me but this view is not the actually count as on any quarter (which should be the count as on the last month of the quarter) rather it shows the number adding all 3 months.

I did create a calculated filed to filter only values for last month of each quarter but it doesn't work if you are in any other month of the quarter like 2, 4, 7, 8, 10, 11 so it returns the numbers well for last year but for this year if I am in say in July then the Q2 2016 should show only July numbers. I am not able to write a logic in that aspect.

Any suggestion and help is highly appreciated.

IF MONTH([Reporting Month])=3 AND year([Reporting Month]) <= year(TODAY()) THEN 1

ELSEIF

MONTH([Reporting Month])=6 AND year([Reporting Month]) <= year(TODAY()) THEN 1

ELSEIF

MONTH([Reporting Month])=9 AND year([Reporting Month]) <= year(TODAY()) THEN 1

ELSEIF

MONTH([Reporting Month])=12 AND YEAR([Reporting Month]) <= year(TODAY()) THEN 1

ELSE 0

END

• ###### 1. Re: Headcount numbers for Quarters

HI

DATETRUNC() would help get the quarters done - so something like this

If you wanted the last day of previous quarter then just add -1

(which should be the count as on the last month of the quarter) rather it shows the number adding all 3 months.

I was confused by this... do you want the average count of employees for the last month of the quarter only?

I would suggest it maybe easier (and more accurate) to take a headcount on a particular day - not the average for the month.

Something like

Cheers

Mark

• ###### 2. Re: Headcount numbers for Quarters

Thanks Mark for the response. Regarding the point below

(which should be the count as on the last month of the quarter) rather it shows the number adding all 3 months.

Since it is headcount data it can't be cumulative numbers. So if I want the view to be a quarterly view the headcount number should always default to the absolute number of last month of that quarter.

For example for Q1 2016 it should be headcount number of March 2016, for Q2 2016 it should be June 2016 and for Q3 2016 it should be July 2016 since we have another 2 months for the quarter end.

• ###### 3. Re: Headcount numbers for Quarters

Do you have solution for this? I'm looking solution for same