I need help with a report: How to create a report that pulls requests that have a deadline of 1 day or less. | Community
Skip to main content
T
TimVa
Level 2
June 15, 2021
Question

I need help with a report: How to create a report that pulls requests that have a deadline of 1 day or less.

  • June 15, 2021
  • 4 replies
  • 1840 views

We have a couple of fields that indicate the deadline depending on the request type/queue topic. "Final Files /needed" and "Preferred Posting Date". How can I create a report that shows requests with these fields being 1 day or less? Seems simple but I am having a tough time figuring it out. I would want to pull this report regularly to show leadership.

I guess it would need to show requests where the submission date and the deadline field are 1 day or less. Would that involve a calculated field? I have not grasped those yet!

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

4 replies

R
RubyAr
Level 2
June 15, 2021

Hi Tim,

You should be able to by filtering the custom field and choosing 'Between' date "$$TODAY-1d" to "$$TODAY"

Hope that works for you.

T
TimVaAuthor
Level 2
June 15, 2021

Thanks Ruby,

Looks like this method would need to be a daily report, if we are looking for requests with a deadline of today or tomorrow, would that be 'Between" $$TODAY" to "$$TODAY+1d"?

If I used "TODAY-1d" to "TODAY" that would show the prior days requests that had the deadlines of yesterday and today?

    R
    RubyAr
    Level 2
    June 15, 2021

    Ahh yes, you're correct. +1d

    T
    TimVaAuthor
    Level 2
    June 15, 2021

    Thanks Ruby I think this is great for a regular daily report, I am actually going to use the advance options to color the row. However, I would also like to add this to maybe a weekly report. The "$$TODAY" wildcard would not work when the report is pulled at a later date. Wondering how to show the difference between the entry date and due date, and if it is 1 day or less set a rule or make that the report parameter.

    R
    RubyAr
    Level 2
    June 15, 2021

    Hi Tim, we created a text mode column that will calculate from Entry Date to the Custom field date:

    Example:

    displayname=Requested Turnaround Time

    textmode=true

    valueexpression=WEEKDAYDIFF({entryDate},{DE:Delivery date required})

    valueformat=

    R
    RubyAr
    Level 2
    June 15, 2021

    NOTE: 'Delivery date required' is our custom field name.

    imgrund
    Adobe Employee
    imgrundAdobe Employee
    Adobe Employee
    June 15, 2021

    Something I've done in the past. I had a group that required 2 day notice and so wanted to report on requests that gave less than that.

    I created a "Compliance Date" which is a calculated field (set to date and time) that has the formula like ADDWEEKDAYS(Entry Date,1) - since you said you want less than one day.

    Then I have a second calculated field called In Compliance? and that formula is IF(Compliance Date>Planned Completion Date,"No","Yes") Although you can replace Planned Completion Date for a Deadline date if you have that in the custom form.

    Worked for my group and hope it works for yours :)

      T
      TimVaAuthor
      Level 2
      June 30, 2021

      Hi Anthony,

      Hoping you may be able to help me out with this. I started using fields like you have, except for mine I used "Rush?" and switched places with the "yes" and "no". So it looks like this:

      Compliance Date:

      ADDWEEKDAYS(Entry Date,2)

      Rush?

      IF(Compliance Date>Preferred Posting Date, "Yes","No")

      And I set a rule in the report we watch that any request that has "yes" for Rush to highlight in Red. This seems to work most of the time but I get random requests where it will say Rush when it isn't or say something is not a rush when it is. I just got a request in that has a Preferred Posting Date of 7/12, the custom form field shows the compliance date would be 7/2, but the Rush field is saying YES. ANy idea what could be causing this?

      imgrund
      Adobe Employee
      imgrundAdobe Employee
      Adobe Employee
      June 30, 2021

      That is weird. I would check do a Recalculate Expression on the project to see if maybe it was a server issue when the request was created. If that still doesn't fix it, I'd contact Support. Something doesn't right with this.

      Doug_Den_Hoed__AtAppStore
      Community Advisor
      Doug_Den_Hoed__AtAppStoreCommunity Advisor
      Community Advisor
      June 15, 2021

      Anthony, I like (and liked) your answer; very nice!

      Tim, because Anthony's "In Compliance?" yes/no data is persisted as Custom Data, you can also Filter, View, Group, Sort, and (personal favorite) Chart by it.

      Taking it Too Far (admittedly, my signature move), you could similarly create a numeric calculated field called "Lead Time In Days", with a formula of ROUND(DATEDIFF(Entry Date, Preferred Posting Date),0), then Filter, View, Group, Sort, Chart based on those deltas, to the nearest day...perhaps by the Person / Department / etc. who's making the request to then reveal (and/or grade, and/or educate, and/or penalize) accordingly.

      Regards,

      Doug

      Terms & ConditionsAccessibility statement

      Loading footer...