Forum Discussion

AshleyAsante's avatar
AshleyAsante
Qrew Cadet
8 years ago

Most Recent WeekDay Field

I need help constructing a formula field (if possible). Each day I import records to a table from another platform, and include a date apart of that record called "Data Date" so that users are able to decipher how recent that data is...

The next day, I import more records, and again, the field "Data Date" includes today's date. So when a user is looking at various reporting, they can filter by data date and see the most recent info.

Most of the time, users want to see records where the Data Date = today. Which is how I've filtered most reports. But if a user ever wanted to see the report on the weekend (which may be very rare), the report is blank.

Are there any fields I can create to look at the data date, and report back the most recent weekday?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    try this as a formula date field

    Case(DayOfWeek([date field]),  // the day of week is 0 for Sunday and counts up from there

    0,[date field]-Days(2), // if Sunday, subtract 2 days to get Friday.
    6,[date field]-Days(1), // if Saturday subtract 1 day
    [date field]) // else just use the date
  • AshleyAsante's avatar
    AshleyAsante
    Qrew Cadet
    8 years ago
    Thank You, --- I'm a bit closer, but not 100% yet.



    I have a field now for MostRecentWeekDay, based on the formula .

    Case(DayOfWeek([date field]),  // the day of week is 0 for Sunday and counts up from there

    0,[date field]-Days(2), // if Sunday, subtract 2 days to get Friday.
    6,[date field]-Days(1), // if Saturday subtract 1 day
    [date field]) // else just use the date


    I'm having trouble writing a report ---My report says, filter on:

    If MostRecentWeekDay = Today

    Or MostRecentWeekDay = 1 day in the past.


    This will show the user all records from yesterday (before I upload the new day's data)... But once I do, I want to get rid of the prior day's data from this report.


    Any ideas?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    OK, I get it.
    I think that the answer is to create another field called [Today or the Previous Friday].
    The formula will be similar,

    Case(DayOfWeek(Today()),  // the day of week is 0 for Sunday and counts up from there

    0,Today()-Days(2), // if Sunday, subtract 2 days to get Friday.
    6,Today()-Days(1), // if Saturday subtract 1 day
    Today()) // else just use the date

    Then filter the report where

    [MostRecentWeekDay] is equal to the value in the field [Today or the Previous Friday]