Forum Discussion

KirstynnRowe's avatar
KirstynnRowe
Qrew Member
2 years ago

Duration Formula

Hello. I use quickbase to track contract request. There are a few different types and based off the type we have a target to get them sent out in. So I have a Received Date Field and a Date Issued Field. I need a formula to tell me if a certain type of request (New Supplier, Existing) was issued with in 4 days, another to tell me if a different type of request (Bidder) was issued in 2 days, and one to tell me if another type of request (Convo) was issued in 2 days. I can figure out how to get a field at the end of my table that says how many days is between the received date and the issued date but I am struggling to determine how to get it to mark if it hit the target based of the request type. 



------------------------------
Kirstynn Rowe
------------------------------

3 Replies

Sort By
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    2 years ago

    You can do this all in one field/formula, or multiple. For clarity, lets use 2 fields.

    The first field would be the Duration. [Duration Field] = [Received Date]-[Date Issued]. This gives your number of days. Note that you may need to add a day to this depending on how you want it to count. For example, June 4-June 3, is that 1 day or 2? QB will say it is one, so if you want it to read as to append + Days(1) to the end of the formula.

    The second field is a check. A formula checkbox for example would be fine.
    [Was Target Hit Checkbox] = 

    var number DurationField = ToDays([Duration Field])  //QB sees days as a numeric value, which you need, versus the original field which QB sees as a duration and doesnt do well in math functions

    //now a formula to check the box
    If(
      ([Type of Request]="Bidder" or [Type of Request]="Convo") and $DurationField<=2, true,
      ([Type of Request]="New" or [Type of Request]="Supplier" or [Type of   Request]="Existing") and $DurationField<=4, true
    )



    ------------------------------
    Mike Tamoush
    ------------------------------

    • KirstynnRowe's avatar
      KirstynnRowe
      Qrew Member
      2 years ago

      Thank you! I was able to get those fields/formulas created. I know have one more question. In my report I was able to get the number of contracts per month that were yes or no to KPI.... Am I able to get a report that shows the different types of request with the yes or no per month?



      ------------------------------
      Kirstynn Rowe
      ------------------------------
      • MikeTamoush's avatar
        MikeTamoush
        Qrew Commander
        2 years ago

        I believe if you use the 'Series' option in your bar chart and select type of request, it will break it down by type



        ------------------------------
        Mike Tamoush
        ------------------------------