Forum Discussion

BobThomas1's avatar
BobThomas1
Qrew Cadet
3 years ago

I need to use the following field in a date range

I need to use the following field in a date range [cln enc date - date] It's a date-formula field. I am trying to check for current dates, within last week (7 days) last week 8-14 days and then 1...
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
3 years ago
Try this as a formula text field to tag the date range.

IF(
[cln enc date - date] > Today(), "Future",
Today() - [cln enc date - date] <= days(7), "Current Week",
Today() - [cln enc date - date] <= days(14), "Last Week",
Today() - [cln enc date - date] <= days(21), "Previous Week", "Older Weeks")







------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
  • BobThomas1's avatar
    BobThomas1
    Qrew Cadet
    3 years ago
    That's perfect Mark. I temporarily changed your values to the beginning of this week, that is 3 days ago, which is Sunday, and then 10 and 17 to check. It works, the last two weeks of data line up. 

    I understand better how to write an IF statement. I'm thinking that I can add to this and using with averages to get the report to print out. 

    I am going to go over Benjamin Buday's Empower 2021 talk on Quickbase Formulas. I will either add more logic to the dates for moving averages, etc. to see if a Summary Report or a regular report is the way to show these.

    ------------------------------
    Bob T.
    ------------------------------
    • BobThomas1's avatar
      BobThomas1
      Qrew Cadet
      3 years ago
      Now that I understand the If statement and used in the Summary Report I can show current provider encounters per week and past weeks, I want to find averages, moving or simple averages to determine trends, such as a drop in encounters from previous weeks (months, days...).

      I created a simple report with just four column fields and labeled. I think I need to use just the middle two columns to create new fields in the report. So I can add current, previous and whatever other time period fields. Then I can perform calculations on those 'actual fields' and create a column showing a positive or negative average. 

      It looks like it would be a query such like SQL or MySQL where a date range with the provider would tally the numbers. 

      I assume it has to be a regular report as the Summary Report doesn't create new fields, but uses transitory field names to display data?

      ------------------------------
      Bob T.
      ------------------------------
      • BobThomas1's avatar
        BobThomas1
        Qrew Cadet
        3 years ago
        I posted this in a message, so posting on my thread.

        I have built two tables in a new App in my Builder Acct. The two tables are Prvdrfullnmes which has all 228 records, total number of providers (doctors or counselors for instance). I have kept the table fields small. I really only need the prvdrfullnme record for each provider, which is 228 presently. The other field in this small table I have called, 'number_encounters_beta. Presently I have it set as a numeric-formula field, no formula at this time.

        I just want to be able to put a specific date, for instance last Tuesday or Wednesday. Each provider will have from 0 to say 30 encounters that day. The average is more like 15 to 18.

        Would I use the Size() function in a query? The larger Encounters table I want to match the prvderfullnme (provider field) with the same name in the prvdrfullnmes table with the field name prvdrfullnme.

        If I can get this number_encounters_beta field to populate with the total encounters for one day, I can make it work for current week, last week, current month, last month, etc.

        Per Mark's help last week, I was able to get good results with a Summary Report, but I want to work on actual fields to find averages, moving averages & other stats.

        If(
        [cln enc date - date] > Today(), "Future",
        Today() - [cln enc date - date] <= Days(7), "Current Week",
        Today() - [cln enc date - date] <= Days(14), "Last Week",
        Today() - [cln enc date - date] <= Days(21), "Previous Week", "Older Weeks")



        ------------------------------
        Bob T.
        ------------------------------
  • TonyaJacques's avatar
    TonyaJacques
    Qrew Member
    2 years ago

    would this formula apply to a field that uses numbers instead of a date but still a range? For example: if ([days open] <= 0-22 and [HIPO/HVL] = "HVL", "In Progress")



    ------------------------------
    Tonya Jacques
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      2 years ago

      Are you trying to check if the numeric value is between 0 and 22?

      If so, then it would be

      IF(

      [days open] >=0 and [days open] <= 22 and [HIPO/HVL] = "HVL", "In Progress")



      ------------------------------
      Mark Shnier (Your Quickbase Coach)
      mark.shnier@gmail.com
      ------------------------------
      • TonyaJacques's avatar
        TonyaJacques
        Qrew Member
        2 years ago

        Yes that worked for me! Thank you!



        ------------------------------
        Tonya Jacques
        ------------------------------