Discussions

Expand all | Collapse all

Create a report that shows time between timestamps

  • 1.  Create a report that shows time between timestamps

    Posted 08-18-2021 12:21
    Hi,

    I need to calculate the time between 2 timestamps

    Time between:
    1. Lead Created and Call Attempt 1
    2. Call Attempt 1 to Call Attempt 2
    3. Call Attempt 2 to Call Attempt 3

    What is the best type of report to do this?

    What is the correct formula (assuming I need to add a formula)?

    I think it may be similar to this thread, but I want to calculate days instead of hours...

    https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=103&MID=31861&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer

    If([Time Out] > [Time In],[Time Out] - [Time In],
    Hours(24) - ([Time In] - [Time Out]))

    Thanks,

    Matt

    ------------------------------
    Mathew Crist
    ------------------------------


  • 2.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 12:27
    Can you give a couple of example of the data you have and the result that you want?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 14:29

    Hi Mark,

    I'm trying to take account of how long we take to call someone from the time a lead comes in (Date created to Call Attempt 1). Beyond that, I would also like to determine how long it has been since the last attempt.

    Knowing this will give us more visibility as to:
    1) how long we generally take to reach out to our leads
    2) When it's time to try again (i.e. Call attempt 1 was 72 hours ago = time to try again).

    Thanks!

    Matt

    ------------------------------
    Mathew Crist
    ------------------------------



  • 4.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 14:34
    Can you give me an example of a single record (say the first one on that list if you like) and the correct calculated result.

    If you just want to know the number of days, then it would be

    ToDays(ToDate([call attempt 1]) - ToDate([Date Created]))

    The above would be a formula numeric field.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 14:46
    Hi Mark,

    Thanks for having me break this down further as it made me realize there's actually 2 problems to solve for.

    Since there's no Call Attempt 1 data yet, let's look at this highlighted example...


    Here, I would want know that it took '1' or '1 day' between Call Attempt 2 and Call Attempt 3.

    It would also be useful to know that since Call Attempt 2 occurred on 08/17/2021, that x number of days has passed since the current date. This could help us know that it's past time for us to reach out again.

    Does that make sense?

    Thanks,

    Matt

    ------------------------------
    Mathew Crist
    ------------------------------



  • 6.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 14:53
    OK, try this

    var date TodayOrLastCallAttempt = IF IsNull([Call Attempt 1]),Today(), [Call Attempt 1]);

    Todays($TodayOrLastCallAttempt - ToDate (Date Created]))

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 14:59
    Thanks Mark!

    ------------------------------
    Mathew Crist
    ------------------------------



  • 8.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 15:17
    Working on it now... would this ideally be a summary report?

    ------------------------------
    Mathew Crist
    ------------------------------



  • 9.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 15:45
    Hi Mark, 

    Also, I'm getting an error message with the code you provided...


    If I update to add the ( in front of IsNull ...




    ...I still get an error that the field is not identified.

    If I change to the name of the timestamp field which is "Call Attempts Timestamp - Message 1" I still get the 'Field is not identified' error message.

    Any advice?

    Thanks,

    Matt



    ------------------------------
    Mathew Crist
    ------------------------------



  • 10.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 15:47
    The formula builder box allows you to choose fields from a drop down list.  I suggest that you use that feature to get the correct field name.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 11.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 16:15

    Thanks Mark. I appreciate your help. I think once I get the formula worked out I can figure out the rest. 

    Can you confirm that a Summary report makes the most sense for this type of request? Or, did you have something else in mind?






    ------------------------------
    Mathew Crist
    ------------------------------



  • 12.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 16:26
    Also, I think I located the correct field name, but now getting this error...



    The field type is Date / Time, so it makes sense, I'm just not exactly sure how I need to modify the code:


    ------------------------------
    Mathew Crist
    ------------------------------



  • 13.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 16:36
    I figured out that I need to define summary variable in order for them to show in the list.

    So, here's where I'm at:



    Also, not really sure if should be choosing distinct count for everything, but it sort of makes sense to do so...



    ------------------------------
    Mathew Crist
    ------------------------------



  • 14.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 16:53
    I'm figuring out that I may need to change the field type to Date, instead of Date/Time. Before I do that, I wonder if there's any way to determine # of Days (or number of hours even) from the Date/Time field?​

    ------------------------------
    Mathew Crist
    ------------------------------



  • 15.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 17:01
    I'm not understanding the question, but the syntax ToDate([my date / time field]) will convert a date / time field to just the Date if used in a formula date field.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 16.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 17:03
    Now completely lost as to what you're trying to do. I thought you were simply trying to find the duration between two fields on a single record. So I don't know why you feel you need to have a summary report.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 17.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 17:17
    Sorry, I thought it was clear that I was talking about a report. Here's the original question...

    I need to calculate the time between 2 timestamps

    Time between:
    1. Lead Created and Call Attempt 1
    2. Call Attempt 1 to Call Attempt 2
    3. Call Attempt 2 to Call Attempt 3

    What is the best type of report to do this?

    What is the correct formula (assuming I need to add a formula)?




    Simply put, I'm wanting to keep track of the time between events. 

    I think I understand where you're going with this. You're suggesting creating a new Formula - Numeric field, instead of a report right?

    And from there, I can create a report that filters by the number of days. That works too. I just need to get the formula to work and having trouble with what you provided. Let me give that a try...




    ------------------------------
    Mathew Crist
    ------------------------------



  • 18.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 17:28
    yes, you need to create a formula numeric field with a formula like this one, but using tour field names.

    var date TodayOrLastCallAttempt = IF IsNull([Call Attempt 1]),Today(), [Call Attempt 1]);

    Todays($TodayOrLastCallAttempt - ToDate (Date Created])) 

    Then once you have the calculated field, it easy to make a regular Table report to look for calls due to be followed up.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 19.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 17:41
    Same issue when creating a Formula - Numeric filed type:



    Getting the error that it is "expecting date but found datetime."

    ------------------------------
    Mathew Crist
    ------------------------------



  • 20.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 17:50
    If you copy and paste you formula I can edit it.  I can't edit a screen shot.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 21.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 18:10
    var date TodayOrLastCallAttempt = IF IsNull([Call Attempts Timestamp - Message 1]),Today(), [Call Attempts Timestamp - Message 1]);

    Todays($TodayOrLastCallAttempt - ToDate ([Date Created]))

    ------------------------------
    Mathew Crist
    ------------------------------



  • 22.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 18:11
    var date TodayOrLastCallAttempt = IF IsNull([Call Attempts Timestamp - Message 1]),Today(),
      ToDate([Call Attempts Timestamp - Message 1]));


    Todays($TodayOrLastCallAttempt - ToDate ([Date Created]))

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 23.  RE: Create a report that shows time between timestamps

    Posted 08-18-2021 18:25
    Looks like it needed a '(' before IsNull...

    IF (IsNull([Call Attempts Timestamp - Message 1]),

    but otherwise that worked perfectly. I appreciate you sticking with me on this!

    ------------------------------
    Mathew Crist
    ------------------------------