Hi WF Community,
I'm trying to create a calculated custom column in a Timesheet report based on the following:
Examples:
Here's what I have for the custom column (text mode), but it's not returning any values:
displayname=Calculated Lieu Time
linkedname=direct
namekey=totalHours
querysort=totalHours
textmode=true
valueexpression=IF({totalHours}>40,({totalHours}-40)*1),IF({totalHours}>43,({totalHours}-40)*1.5,"")
valueformat=int
Does anyone know where I might be going astray?
Thanks.
Nick
Solved! Go to Solution.
Topics help categorize Community content and increase your ability to discover relevant content.
Hi Nick,
Try the below code, this should do it:
displayname=Calculated Lieu Time
linkedname=direct
namekey=totalHours
querysort=totalHours
textmode=true
valueexpression=IF({totalHours}>43,SUM(PROD(SUB({totalHours},43),1.5),3),IF({totalHours}>40,SUB({totalHours},40)))
valueformat=double
What this is doing is, if the hours logged > 43, we are deducting 43 from the total hours and multiplying the remainder by 1.5, then adding 3 to that total (as we are multiplying by a factor of 1, we don't need to multiply - Ive also removed the multiply function from the second part of the expression for the same reason). That should cover your requirement.
One question I have though is how are you measuring time in lieu? Is it anything over 40 hours, or anything over 41 hours? In the expression in your original post you were looking for hours that were > 40, but you describe the calculation as being anything over 41 hours. If it anything over 41, you will want to use the following value expression instead (changes in bold):
valueexpression=IF({totalHours}>43,SUM(PROD(SUB({totalHours},43),1.5),2),IF({totalHours}>41,SUB({totalHours},41)))
Does that make sense?
Best Regards,
Rich.
Hi Nick,
Try the below code instead:
displayname=Calculated Lieu Time
linkedname=direct
namekey=totalHours
querysort=totalHours
textmode=true
valueexpression=IF({totalHours}>43,PROD(SUB({totalHours},40),1.5),IF({totalHours}>40,PROD(SUB({totalHours},40),1)))
valueformat=double
A couple of pointers for you:
Best Regards,
Rich.
Thanks for this, Richard. It worked properly based on how I described the need. However, I just realized there was a flaw in how I described it. Wondering if you have any thoughts on an alternative, based on the following:
Example:
Views
Replies
Total Likes
Hi Nick,
Try the below code, this should do it:
displayname=Calculated Lieu Time
linkedname=direct
namekey=totalHours
querysort=totalHours
textmode=true
valueexpression=IF({totalHours}>43,SUM(PROD(SUB({totalHours},43),1.5),3),IF({totalHours}>40,SUB({totalHours},40)))
valueformat=double
What this is doing is, if the hours logged > 43, we are deducting 43 from the total hours and multiplying the remainder by 1.5, then adding 3 to that total (as we are multiplying by a factor of 1, we don't need to multiply - Ive also removed the multiply function from the second part of the expression for the same reason). That should cover your requirement.
One question I have though is how are you measuring time in lieu? Is it anything over 40 hours, or anything over 41 hours? In the expression in your original post you were looking for hours that were > 40, but you describe the calculation as being anything over 41 hours. If it anything over 41, you will want to use the following value expression instead (changes in bold):
valueexpression=IF({totalHours}>43,SUM(PROD(SUB({totalHours},43),1.5),2),IF({totalHours}>41,SUB({totalHours},41)))
Does that make sense?
Best Regards,
Rich.
That does make sense, thanks, Rich! Super helpful.
I'm going to have to go back to the requesting stakeholder to see if it's +40 or +41, but it's great you provided both. I shall take this learning forward.
The other part of this overall ask is here. No obligation, obviously, but if you have time and have an idea of whether this is possible, feel free to share your thoughts. Basically, the next step is trying to exclude hours from certain tasks from coming into this report.
Cheers.
Views
Replies
Total Likes
A possible solution to the 2nd part of my ask (noted in my previous message above), is to adapt the text mode you provided above, and make it work within an Hours report (as opposed to a Timesheet report). I tried the following modifications to the text mode, and created the column in a Hours report, but It's not returning any results.
displayname=Calculated Lieu Time
linkedname=timesheet
namekey=view.relatedcolumn
namekeyargkey.0=timesheet
namekeyargkey.1=totalHours
querysort=timesheet:totalHours
textmode=true
valueexpression=IF({timesheet:totalHours}>43,SUM(PROD(SUB({timesheet:totalHours},43),1.5),3),IF({timesheet:totalHours}>40,SUB({timesheet:totalHours},40)))
valuefield=timesheet:totalHours
valueformat=double
Views
Replies
Total Likes
Hi Nick,
Sorry to say that I don't think that this is possible in Workfront, or not that I know of anyway.
Since the Timesheet report will surface the total of all hours logged hours within a certain date range as a single report entry, the value expression I created works nicely in this scenario.
An hour report on the other hand will pull in every hour object (not just the total), so this value expression wont work in that instance because none of report entries are going to be more than 40 hours (unless your colleagues have invented a time machine to work 40+ hours in a day! 😁).
I think an hour report is probably the right way to go if you need to filter out certain hour types, but you wont be able to automate the calculation of time in lieu using this. I hate saying this (as it feels like an blasphemy!), but this might be one of those situations where you'd need to create a hour list report and export into excel to carry out the time in lieu calculations.
Sorry I couldn't help further.
Best Regards,
Rich
Richard Leek, you have served your compadres admirably :). Thanks for the help.
Views
Replies
Total Likes