Filter only on Weekends or Weekdays

[pb_si]

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?

[AssafHanina · Accepted solution]

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]

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

[AssafHanina]

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]

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]

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]

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!)