Excluding Weekends on Date filter in a Widget

Answered

Follow

Raj Goli

September 17, 2018 15:09

Hi,

 

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

0

• 

  Megan Moss September 17, 2018 15:58

  We do this via a date dimension table. We have a binary field that flags the date by if it’s a business day or not and you can filter out the non-business days.

  0

  Comment actions Permalink
• 

  Richard Stubbs September 20, 2018 10:31

  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)

  0

  Comment actions Permalink
• 

  Peach Bluhm February 11, 2020 17:51

  is there a way to alter this filter to do the same and select only the previous day over weekends?

  {
    "last": {
      "count": 1,
      "offset": 0,
      "anchor": "last"
    },
    "custom": true
  }

  1

  Comment actions Permalink
• 

  Janet Erdman September 29, 2020 14:17

  I need to be able to do the same. Has anyone figured this out yet?

  0

  Comment actions Permalink
• 

  Ido Darnell September 30, 2020 07:37

  Hi Peach BluhmJanet Erdman

  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

  ido.darnell@qbeeq.pl

  QBeeQ

  0

  Comment actions Permalink
• 

  Ravid Paldi September 30, 2020 11:35

  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? 
  
  

  0

  Comment actions Permalink

Please sign in to leave a comment.