New function for Replicon please! (=Sum)

  • 1
  • Idea
  • Updated 5 years ago
  • Not Planned
I need a new function, or any other solution close to what I need. I am trying to create a report where it shows the hours utilizes for each project by each department. The columns would include, department name, project name & code, hours punched for each project, THEN I need a formula that calculated those hours divided by the total hours for that period. In other words, I need a column that calculates the ratio of how much time the department spent on that project against all the hours/projects. 

i.e.
Column

1                            2                            3
Project A               20 Hours                40%
Project B               20 Hours                40%
Project C              10 Hours                 20%

I need a solution to find out column 3. Each department needs to know in percentages what projects their team spent time on and Replicon doesn't have a solution for that yet, please create one for us! Thank you.
Photo of Max Terrien

Max Terrien

  • 2 Posts
  • 0 Reply Likes
  • Frustrated

Posted 5 years ago

  • 1
Photo of Lingaraj Dharwad

Lingaraj Dharwad, Product Champion

  • 1100 Posts
  • 46 Reply Likes
Hi Max,

Thank you for using Replicon community,

We are reviewing your idea.

Thanks,
Lingaraj
Photo of Naveen Chander

Naveen Chander, Product Manager

  • 39 Posts
  • 12 Reply Likes
Official Response
Hello Max,

I reviewed your requirements and I believe we should be able to achieve this using the Timesheet Day report template with a custom column/formula. This report template has the 'In Period' type fields that present the aggregate for the entire timesheet period, which can be used for this calculation.

I was able to build a sample report to test the idea and it looked like this: 



I created a new custom column in the report and used the below formula. The custom column format was set to Percentage.
If (IsNull (ProjectHoursInPeriod) || ProjectHoursInPeriod = 0, 0, ProjectHours/ProjectHoursInPeriod)

I hope this helps. Should you need further assistance in configuring this report, please let us know and one of our Support specialists could get in touch with you.


Thanks,
Naveen
Photo of Max Terrien

Max Terrien

  • 2 Posts
  • 0 Reply Likes
Hi Naveem

This still does not solve my issue, since I am trying to pull actual hours. I want to go by staff, not by department (Apologies if I wasn't clear on this part). So in a sense, I want to see how much one employee utilized their time against a project, shown in percentages.
For example:

Project ID         Project Description       Dept.           Name           Actual Hours               Staff Utiliy

ABC001            ABC - 001                    HR              Joe               10                                20%
ABC002            ABC - 002                    HR              Joe               10                                20%
ABC003            ABC - 003                    HR              Joe               10                                20%
ABC004            ABC - 004                    HR              Joe               20                                40%
                                                                                                                                       = 100%

ABC001            ABC - 001                    HR              Justin            10                               20%
ABC002            ABC - 002                    HR              Justin            20                               40%
ABC003            ABC - 001                    HR              Justin            20                               40%
                                                                                                                                       = 100%

So from this, I can see that Joe spent 40% of his time on ABC004, by selected dates. I can also see that Justin spent 40% of his time on ABC002 & ABC003. 



               
Photo of Naveen Chander

Naveen Chander, Product Manager

  • 39 Posts
  • 12 Reply Likes
Official Response
Hello Max,

Thank you for the additional detail. 

I believe this should be doable in the same report by simply including the User Name column in it.
The granularity of the report would go down to a per-user level when you select the user name column. This will then show values for columns like Project Hrs, Project Hrs (In Period) for each corresponding user. Subsequently, the same formula would also show the percentage worked by each user against the overall project hours logged by that user in the selected period.


Thanks,
Naveen