Text Mode Question

[SarahNau]

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=&#xfeff;

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=&#xfeff;

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!!

[AnthonyIm]

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?

[Heather_Kulbacki]

Similar to Anthony's answer, it looks like you're trying to do a calculation on what is reported in two different columns of the same report. And you can only do a calculation on actual fields. I think you'd need to create a custom calculated field to get the data for your first two columns, then in the third column so a calculation between those two new fields.

[SarahNau]

I don't think you can reference collections in a calculated field - can you?I was thinking the same as Anthony, but wanted to at least ask!

[JosueMe]

No, you can not reference a collection in a calculated field. So logically speaking, I think you want to go from a task to the project level and then grab the custom form data value from a specific data field and compare that to the value field in the original task. I do think that it's technically possible. I've done similar from going from an issue to tasks on a project. What you definitely need though is some distinct flag, that when the valueexpression is looking at the task collection, it will pick up only the week's prior. The solution I'm thinking would be on a task report. You however are trying to do it on a project report. That would involve referencing the collection, and then within that referencing the collection again to compare against itself. I don't think that's possible.