3 Replies Latest reply on Sep 9, 2016 12:18 PM by David Li

    New employee performance over their first year

    Cindy Hill

      I need to create a report that compares new employee to average of all employees during the same first year period. New employees are charged with selling a certain dollar value over the first 3, 6,9 and 12 months. We need to have a report that shows the $ produced, month to month, for the selected employee for the first 3 months and overlay the average of all the other employees who completed their first 3 months. Same for 6,9 and 12 months.


      Any ideas on how to go about this? Attached is a sample set of data. The data is broken out by 1 row = 1 job.

        • 1. Re: New employee performance over their first year
          David Li

          Hi Cindy, how does this look?

          To compare the sales along each employee's tenure, we just get the difference between the Date Produced and Start Date for each employee and match along that. An LOD calculation calculates the average dollars produced.


          This is just for the first 92 days (approximately 3 months). To get the other periods, you just change the [Days Since Start] filter (on its Condition tab).

          • 2. Re: New employee performance over their first year
            Cindy Hill

            Thank you David! The Days Since Start works great. I actually used it, removed the condition but instead created groups (0-90 day, 91-180 days, etc). Issue I am having though is with the averages. The report needs to show 1 employee at a time but still show the average off all the employees in the data set.


            Thanks for your help!


            • 3. Re: New employee performance over their first year
              David Li

              You're welcome, Cindy! To get around the issue caused when you'd like to see an average of all employees while filtering in only one, you can use a LOD calculation with the FIXED keyword, which ignores normal filters (but not context ones). The original solution I posted used EXCLUDE, but that doesn't ignore filters. The new average looks like this:

              { FIXED [Days Since Start Group] : SUM([Dollars Produced])/COUNTD([Franchisee Owner Name]) }

              Also, I wanted to mention that creating groups is a great idea, but you should remember that if you break down the data by those groups (e.g. by putting it on the Columns Shelf), the values for each cell will not be inclusive of other tenure length groups. That is, the value in the 91-180d group won't include the 0-90d values. The easiest way to get around this is either to use the groups only as a filter (so that there's no breakdown on the sheet) or to use a running total across groups. Let me know if that's unclear.