0

Filter

Hi Team,

How can I create a date filter for “previous working day”?
I need it so that when a user logs in on Monday, the “previous day” filter returns Friday’s data (skipping Saturday and Sunday). I also want the filter to work over the last 45 days and current date.

3 replies

null
    • Customer Solutions Architect
    • Moshe_Yossef
    • 2 days ago
    • Reported - view

    Ok, let's break it down.

    First, - to find today as a date we use the date() function, to which we can add days using addDays().
    Next we need to figure out how to take a date and convert it to an element in the date column - you need to convert it to a timestamp and use StrToDate:

    StrToMember([Dates].[Date],DateToTimeStamp(date())) /* today */
StrToMember([Dates].[Date].[dt],DateToTimeStamp(addDays(date(),-1))) /* yesterday */

    The next step is to find out what day it is today - you can use DateFormat() function with "E" for the masking to receive the abbreviated day name.

    and lastly - you need to use an if statement:

    {if(
DateFormat(date(), "E") = "Mon",
{StrToMember([Dates].[Date],DateToTimeStamp(AddDays(date(),-3)))},
    if(DateFormat(date(), "E") = "Sun",
    {StrToMember([Dates].[Date],DateToTimeStamp(AddDays(date(),-2)))}, /* for Sunday - go back 2 days*/
    {StrToMember([Dates].[Date].[dt],DateToTimeStamp(AddDays(date(),-1)))} /* for any other case - go back 1 days*/
    )
)
}

    I hope this helps,

    Moshe

      • Customer Solutions Architect
      • Moshe_Yossef
      • 2 days ago
      • Reported - view

      Notice my example uses a list - you may want to create it as a calculation (in which case you need to lose all the curly brackets).

      • sanjay.1
      • 2 days ago
      • Reported - view

       Thank you 
      this is working fine 

Login to reply

Content aside

  • Status Answered
  • 2 days agoLast active
  • 3Replies
  • 14Views
  • 2 Following