Excluding Weekends on Date filter in a Widget
AnsweredHi,
Can someone help me if there is way for Sisense to only consider Weekdays on a date filter?
I am expecting my date filter to show data for previous day on my date filter but i want the filter to be smart enough to exclude Sunday and Saturday and show data for Friday. Any help is much appreciated.
Thanks,
Raj
-
Hi,
Assuming you want the dashboard to always show the data from the previous working day, we achieved this by creating an custom query in the Elasticube that identifies the previous working day at the time of the build:
SELECT
max(date_field) AS PreviousBusinessDayDate,
1 AS IsPreviousBusinessDay
FROM
Dates_table d
WHERE
d.date_field < adddays(now(), -1)
AND
dayofweek(date_field) NOT IN (6,7)That creates a custom table with the fields PreviousBusinessDayDate and IsPreviousBusinessDay. The table only has one row. Connect this to the main dates table using the field PreviousBusinessDayDate, and in the dashboard put a filter for IsPreviousBusinessDay = 1
Then your dashboard will always show data for "yesterday", unless yesterday was a Saturday or Sunday in which case it will show data for last Friday
(note that if you have changed the "First Day of Week" in your admin settings, then you'll need to change the (6,7) to something else - I think (1,7) if youve selected Sunday)
-
This can be achieved by use of the date dimension file, you can find it in this article
You can also use the DAYNAME(number) and DAYOFWEEK(datetime) which together will return the names of the days of the week,
Then filter out the weekend days.
LMK if you have any further questions,
Feel free to reach out
QBeeQ
-
Hi Janet Erdman,
Richard Stubbs suggestion should work (maybe with few tweaks to the query that build the table).
Can you elaborate on what's currently not working?
Please sign in to leave a comment.
Comments
6 comments