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 daysand then 15-21 days.I try Boolean logic with AND and use ToDate, Today, etc but getting errors, such as mixing date and time. I just want date calculations and don't care about time of day. I have image of the date fields there are. There are a lot of date fields, but I didn't create most of these. The [cln enc date - date] is the field that is used to match encounter records.------------------------------Bob T.------------------------------

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

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

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

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

My latest understanding of using Quickbase formulas, specifically using a query(s) to fill a field(s) in one table of at present 228 records. I will call them doctors or behavior therapists which is the hospitals business with both adults and adolescents. The doctor table (name is prvdrfullnmes for the current 228 providers). So I have just 228 records with several fields. I just want to match each provider with the number of patients (encounters) he has had, yesterday, the day before yesterday, last week, month, etc. So I will have a few fields devoted to encounters that he/she had recently. I would call those fields dynamic as the data will change daily, as yesterday will become the day before yesterday tomorrow.It is easier to think of the data as a snapshot of what it is today. AND this I have done in a summary report. BUT the summary report is limited in doing calculations and statistics.The big table of data to feed the prvdrfullnme table I am trying to use a query of the prvdrfullnme field which both tables have. So if Benjamin Spock in the Encounters table had 15 encounters yesterday, then Benjamin Spock in the prvdrvullnme table will show 15 encounters in the yesterday field. What I have so far in a numeric formula field is this. I know it needs work, probably a lot. I am trying to think like you would use SQL or MySQL or other query language. Since QB has it's own proprietary way of doing formulas and now it can do queries on more than one table, this is what I have gathered. I'm going to take a short break on this tonight, and come back tomorrow afternoon. If( var number noEncounters = Size(GetRecords("{13.EX. 'Today() - [cln enc date - date] = Days(2)'}", [_DBID_ENCOUNTERS]));AND ({[prvdrfullnme].EX.[prvdrfullnme]},[_DBID_ENCOUNTERS],@noEncounters) ) #------------------------------Bob T.------------------------------

I need to use the following field in a date range

11 Replies

MarkShnier__You
Qrew #1 Challenger
2 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
  Qrew Cadet
  2 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
    Qrew Cadet
    2 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.
    ------------------------------
- TonyaJacques
  Qrew Member
  5 months 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
    Qrew #1 Challenger
    5 months 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
    ------------------------------

Forum Discussion

I need to use the following field in a date range

11 Replies

Related Content

Sorting a date range as string

CASE Function usage with ranges

Following Links into the Mobile App

Next Work Date/Follow-up Field

Add Field Total for Date Range

Recent Discussions

An API(?) button to copy fields from the form, to other fields

Pipeline Make Request - Return Make Request JSON Output into fields

Formula using Contain

Moving data from a columnar table to a record based table

Create Child Record, Save and then Edit button