Discussions

 View Only
  • 1.  I need to use the following field in a date range

    Posted 5 days ago
      |   view attached
    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 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.
    ------------------------------


  • 2.  RE: I need to use the following field in a date range

    Posted 5 days 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
    ------------------------------



  • 3.  RE: I need to use the following field in a date range

    Posted 5 days 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.
    ------------------------------



  • 4.  RE: I need to use the following field in a date range

    Posted 4 days ago
      |   view attached
    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.
    ------------------------------