Forum Discussion

JabreyHowell's avatar
JabreyHowell
Qrew Trainee
7 years ago

Reporting a number of days the status of a job was running between 2 custom dates.

I have fields for the following
Job ID
Status
Start
Stop

At any time, I can get a request to count and report on my job days.    My definition of a job day is a job that is running on that day is 1.    

However, if I need to run this report from the 8th of the previous month through the 8th of this month, I don't want the days the job was still running before or after this date to be counted in the total number of job days.     

How would I get this to track accurately and be able to pull the report easily.   
App builders
Formulas and functions
reports and charts
  • ForrestParker's avatar
    ForrestParker
    Qrew Cadet
    7 years ago
    I can think of two options depending on the requirements.

    1) If you need to have complete control over the date range every time you run the report, then set the date filters to <ask the user>


    2) If you always need to count the job days during the previous "month" from the current day then create a Field that calculates the date that is a month before the current day and use that as your "is on or after" filter.  The "is on or before" filter would be "current day".  

    The one month ago formula would be a formula - date field and would look like the following:

    AdjustMonth([Date],-1)

  • JabreyHowell's avatar
    JabreyHowell
    Qrew Trainee
    7 years ago
    I like that possibility. 
      
    During the wait for assistance, I created 2 new Formula Date fields.   Reporting Start Date and Reporting Stop Date.   

    The Reporting Start Date field looks at if the actual Start Date field is >30 days ago, it will use 30 days ago as the Reporting Start Date. 
             My formula   
      Max([Start],(Today()-Days(30)))

    The Reporting Stop Date will look at the Actual End date, and if it is populated it will use it, otherwise it will use Today. 
             My formula
    Min([End],Today()) 

    Then, I set up to filter the jobs to those that had a Start or End date that is within the last 30 days.    

    So far, I think it is working correctly.