1

I am trying to create a saved search of time entries in Netsuite.

Under criteria, I have specified a date range. This varies as this is an available filter

For the purposes of this example, the date range is 1/4/2020 to 10/4/2020

The first column ('Total Customer Billable Hours') in this sums all time entries that are coded against project task type 'billable project'. The formula I am using for this:

Formula (Numeric), sum, Case when {project.task_type}='Billable' then {durationdecimal} else 0 end

For the second column, I want the sum of hours the employee would normally work (in the time period specified under criteria-1/4/2020 to 10/4/2020 in this example)

The formula I am using to sum this is

Formula(numeric), sum, {timesheet.workcalendarhoursdecimal}

However, this is multiplying the employee's weekly hours by the number of time entries that make up the 'Total customer billable hours' figure

i.e. if an employee works a 40 hour week, the formula is multiple 40 x 36 (the number of time entries that make up the customer billable figure for example)

What would the correct formula be so that the second column is only summing the employee's work calendar hours for the period specified in the criteria/available filter selection?

Vernita
  • 93
  • 3
  • 15

1 Answers1

0

Try changing sum to maximum:

Formula(numeric), maximum, {timesheet.workcalendarhoursdecimal}

zerecees
  • 697
  • 4
  • 13
  • I've tried that though I am getting 40 (which is equal to one week worth of working hours) instead of 78 (40 hours for week 1 and 38 hours for week 2) – Vernita Apr 19 '20 at 03:52