Hi All,
Here is challenge for all of you text moders out there! I have been asked for a report that compares a two task custom form entries. The tasks are the same week to week, with the same custom field (Pristine). They want the report to show the entry from last week, this week, and then the difference. I created a project report and was able to get the first and second columns:
Last Week:
displayname=Pristine Last Week
listdelimiter=
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF(CONTAINS("Enter Resyndication Qualified Unit Update",{name}),IF({plannedCompletionDate}<$$TODAYe-1w,IF({plannedCompletionDate}>$$TODAYb-1w,{DE:Pristine},"")))
valueformat=HTML
This Week:
displayname=Pristine This Week
listdelimiter=
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF(CONTAINS("Enter Resyndication Qualified Unit Update",{name}),IF({plannedCompletionDate}<$$TODAYew,IF({plannedCompletionDate}>$$TODAYbw,{DE:Pristine},"")))
valueformat=HTML
But, when I try to get the difference between these, it is not working:
displayname=Pristine Difference
listdelimiter=
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF(ISBLANK(IF(CONTAINS("Enter Resyndication Qualified Unit Update",{name}),IF({plannedCompletionDate}<$$TODAYew,IF({plannedCompletionDate}>$$TODAYbw,{DE:Pristine},"")))),"",SUB(IF(CONTAINS("Enter Resyndication Qualified Unit Update",{name}),IF({plannedCompletionDate}<$$TODAYe-1w,IF({plannedCompletionDate}>$$TODAYb-1w,{DE:Pristine},""))),IF(CONTAINS("Enter Resyndication Qualified Unit Update",{name}),IF({plannedCompletionDate}<$$TODAYew,IF({plannedCompletionDate}>$$TODAYbw,{DE:Pristine},"")))))
valueformat=HTML
I have broken down my valueexpression and everything works except the 'Pristine Last Week' calculation. If I replace that with a number, the rest of the code works. Any ideas would be appreciated!!
Topics help categorize Community content and increase your ability to discover relevant content.
Views
Replies
Total Likes
Hi - I'm not sure what you are doing is possible. It sounds like you are trying to do a calculation based on two different tasks. A collection just looks at a single task at a time and if it means the IF statement's criteria, then it will appear. One task can't have a planned completion date both last week and this week.
I was thinking maybe there is something you can do with a task report, but with a grouping aggregation you only have average and sum, not sub. Maybe there is something you can do with charts that shows the difference week to week?
Anyone else have any thoughts on this one?
Views
Replies
Total Likes
Views
Replies
Total Likes
Views
Replies
Total Likes
Views
Replies
Total Likes
Views
Likes
Replies
Views
Like
Replies
Views
Likes
Replies