Discussions

Expand all | Collapse all

Most Recent WeekDay Field

  • 1.  Most Recent WeekDay Field

    Posted 03-12-2018 17:17
    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?


  • 2.  RE: Most Recent WeekDay Field

    Posted 03-12-2018 19:27
    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


  • 3.  RE: Most Recent WeekDay Field

    Posted 03-13-2018 16:17
    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?


  • 4.  RE: Most Recent WeekDay Field

    Posted 03-13-2018 17:17
    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]