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 16, 2021

      Thank you Anthony,

      I think I am getting the hang of your solution. Do you know if there is a way to do an "IF, OR" calculation formula?

      For example we have one request form that has multiple options. Is it possible to do IF(Compliance Date>Planned Completion Date,"No","Yes"), OR IF(Compliance Date>Final FIles Needed,"No","Yes")

      To account for both possibilities?

      imgrund
      Adobe Employee
      imgrundAdobe Employee
      Adobe Employee
      June 16, 2021

      Hi Tim - totally possible. You just put the second condition inside the first IF statement. If it is OR you put it in False (which is Yes for you) and if it is AND you put it in True (which is No for you).

      So in your case...

      IF(Compliance Date>Planned Completion Date,"No",IF(Compliance Date>Final FIles Needed,"No","Yes"))

      So if the Compliance Date is greater than Planned Completion Date, it will display no. If it is less than, then it will look to see if the Compliance Date is greater than Final Files Needed field and put Yes or No.

      Hope that helps

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