Text mode filter help

[GinaTu]

Hi there!

I'm trying to create a report to show any/all projects that come in with a turn-around time (target live/due date) that is under two weeks from entry date (request date). Is there a text mode filter out there that anyone has used?

Thanks!

[Richard_Le_]

Hi,

To achieve this, you would need to add a calculated field to your projects that calculates the number of days between the request entry date and the planned Completion Date of the project (assuming that plannedCompletionDate is the same as the target live date). The calculation would look something like this:

WEEKDAYDIFF({convertedOpTaskEntryDate},{plannedCompletionDate})

Make sure that you set the format of the calculated field to number type to ensure that it can be used correctly in reports.

You can then reference this field in your report filters and looks for values that are less than 10.

Hope this helps!

Best Regards,

Rich.

[GinaTu]

Thank Rich. 

So how would this work if I'm using a custom field? The built-in "planned completion date" is often different from our "Target/Live Due Date" ?

[Richard_Le_]

It would just be a case of replacing plannedCompletionDate with the field name in the calculation.

So if your field is named Target/Live Due Date, the calculation would look like this:

WEEKDAYDIFF({convertedOpTaskEntryDate},{DE:Target/Live Due Date})

Best Regards,

Rich.

[GinaTu]

I'm still confused. Apologies if my ask was confusing. 

So am I able to create a column in my report that shows the number of days or do I need to create the calculated field in my custom form? I was hoping to put it as a column on my report.

[Richard_Le_ · Accepted solution]

No problem, Gina.

In your original post, you said that you wanted to apply a filter to your report so that only projects with a turnaround time of less than 10 days are shown. The steps that I have outlined above will allow you to do this.

If you also want to show the number of days between the request entry date and Target Live date, you can simply add the calculated field you created (for use in the filter) as a column to your report view.

Does that make sense?

[GinaTu]

Yes, that all makes sense now.  But I keep getting N/A in my calculated field.

What might I be doing wrong?

[GinaTu]

ACK!!

Now I've got it working on some. For some I still get N/A and for others I'm getting negative numbers. All of our projects are set up exactly the same way, with the same fields. Any idea what could be happening?

[Madalyn_Destafney]

You may just need to reverse the calculation? Try

WEEKDAYDIFF({DE:Target/Live Due Date},{convertedOpTaskEntryDate})

[GinaTu]

Thx Madalyn,  but that gives me the same results.

[Madalyn_Destafney]

Darn. Is the custom field name exactly as you have it in the text mode, and no space after DE:?

DE:Target/Live Due Date

[GinaTu]

Yep. You can see it works for some and not for others. And some are blank with no numbers at all.

[Madalyn_Destafney]

In your calculated custom form field, try selecting the checkbox towards the bottom to run/apply precious calculations (I forget actual verbiage)…then save and then refresh your report. Any luck?

[GinaTu]

Nope. Same. 

[Richard_Le_]

Hi Gina,

The fact that the code is returning results on some projects but not others shows that the code is correct. Therefore there must be something different about the projects with no results. This is going to be one of the following:

1. The project does not have an originating request that it was converted from
2. The project does not have the Target/Live Due date populated
3. The project has a different form attached that doesn't have the calculated field on it.

I can see in your screenshot that he calculation is returning a value with lots of decimal places. We can fix this (if that would be useful), by adjusting the code as follows:

ROUND(WEEKDAYDIFF({convertedOpTaskEntryDate},{DE:Target/Live Due Date}),0)

Adding the "ROUND" expression will round the value to 0 decimal places, so that you get a value rounded to the nearest day.

Best Regards,

Rich.

[GinaTu]

So I let it marinate over night and now it seems to be working! Very weird!

Thanks for all your help!

[LaurenWood]

We currently have an open Support Case about the "Update previous calculations (in the background)" not working and we were told that the development team opened a bug on Sept 7th that stops the "update previous calculations" from finishing. (However, it would work if we recalculated the expressions directly on each project.) If it continues, you may consider opening a support case as well to see if you can have your issues looked into, so it can be linked to the bug if needed.