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

    Headcount numbers for Quarters

    Dibyendu Sharma Mondal

      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
          Mark Fraser

          HI

           

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

          DATETRUNC('quarter',[your date])

           

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

          DATETRUNC('quarter',[your date])-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

          IF [your date] = DATETRUNC('quarter',[your date])-1 THEN COUNTD([employee]) ELSE NULL END

           

          Cheers

          Mark

          • 2. Re: Headcount numbers for Quarters
            Dibyendu Sharma Mondal

            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
              Rupali jain

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