Forum Discussion

pb_si's avatar
pb_si
Cloud Apps
11-19-2024
Solved

Filter only on Weekends or Weekdays

I have a need to filter based on day of week - for example, I need to total all weekend (or weekday) sales. The filter would seem to be the way to go, but I can't find a way to get it to filter base...
dashboards & reporting
  • AssafHanina's avatar
    AssafHanina
    11-19-2024

    Hey pb_si ,

    The recommended best practice for retrieving this data is to include a Date Dimension directly in the data model, which is then joined to the relevant Fact tables.

    Dim Date includes the Date column along with related columns such as:

    • Days_of_week
    • Is_weekend or Weekend/Weekday 

    With this approach, instead of filtering by a specific date, the Weekend/Weekday column can be used as a dashboard filter. This also allows the option to group by this column (see the related screenshot below)

    Alternatively, in case Dim_date is not available, the Next Approach is to Create a Custom Column in the data model(Elasticube) or directly in the Source DB With the Weekend/Weekday values. 
    Here is an example of Custom Column for Elasticubes: case when DayOfWeek(Date) in (1,7) then 'WEEKEND' Else 'WEEKDAY' END

     

    Best Regards

     

AssafHanina's avatar
AssafHanina
Sisense Employee
11-19-2024
Copy Link

Hey pb_si ,

The recommended best practice for retrieving this data is to include a Date Dimension directly in the data model, which is then joined to the relevant Fact tables.

Dim Date includes the Date column along with related columns such as:

  • Days_of_week
  • Is_weekend or Weekend/Weekday 

With this approach, instead of filtering by a specific date, the Weekend/Weekday column can be used as a dashboard filter. This also allows the option to group by this column (see the related screenshot below)

Alternatively, in case Dim_date is not available, the Next Approach is to Create a Custom Column in the data model(Elasticube) or directly in the Source DB With the Weekend/Weekday values. 
Here is an example of Custom Column for Elasticubes: case when DayOfWeek(Date) in (1,7) then 'WEEKEND' Else 'WEEKDAY' END

 

Best Regards

 

  • pb_si's avatar
    pb_si
    Cloud Apps
    11-20-2024
    Copy Link

    Thanks AssafHanina this might be a way to go, need to think through best approach if we go this route.

    • AssafHanina's avatar
      AssafHanina
      Sisense Employee
      11-20-2024
      Copy Link

      You are welcome pb_si .

      in case needed you can also review the Dim_Date article which contain the Date Excel File for reference

      Best Regards