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 based on weekday. I tried JAQL exclude, which it parsed ok but didn't seem to work correctly.

Any other ideas?

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

     

7 Replies

  • AssafHanina's avatar
    AssafHanina
    Sisense Employee
    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

     

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

      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

        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

  • Jake_Raz's avatar
    Jake_Raz
    ETL
    11-20-2024

    One way to do this, without needing to make changes in the data model, is to calculate the day directly from the date. There's various ways you can derive the day of the week from a given date. For example, see here (I don't think that's the only one, I saw some others while searching).

    With a bit of experimentation you can figure out how to convert the algorithm into the equivalent formula(s) in Sisense and create a Measured Value that will output the number of the day of the week (e.g. Sunday is 0, Monday is 1, Tuesday is 2, etc). Then you can filter based on that.

    • Jake_Raz's avatar
      Jake_Raz
      ETL
      11-20-2024

      Hmm, actually, I'm not sure how to do with this formulas. You'd need to somehow output numbers based on the date values, and I can't find a way to do that. For example, if we're trying to find the year. In the formula editor, none of the listed formulas will return a year number (e.g. "2024") from a given date. I found a page in the Sisense documentation that mentions a "GETYEAR" function, but evidently that only works if you're modifying the data model; if you try to use the same formula in the front-end, it just doesn't work (errors out). Another thing I thought might work would be to use the YDIFF formula to calculate the year difference between a set date (say, 1/1/2000) and the target date field. However, there's no way to enter a static date in the formula, it only wants to accept a date value from a field of some sort.

      Another thing to consider would be using a widget script to calculate everything instead. However, I'm not sure how to do that. Sorry!

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

        Thanks Jake_Raz, this was my first choice - really, I'd prefer to do this in UI layer to avoid adding maintenance in the model, but I'm not sure it's possible without changes to formula interpreter (unless anyone knows different!)