Forum Discussion

Qrew Cadet

2 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

Qrew Champion

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

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.
------------------------------
- BobThomas1
  Qrew Cadet
  2 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.
  ------------------------------
  - BobThomas1
    Qrew Cadet
    2 years ago
    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.
    ------------------------------