Excluding Weekends on Date filter in a Widget

Answered

Comments

6 comments

  • Avatar
    Megan Moss

    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
  • Avatar
    Richard Stubbs

    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
  • Avatar
    Peach Bluhm

    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
  • Avatar
    Janet Erdman

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

    0
    Comment actions Permalink
  • Avatar
    Ido Darnell

    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
  • Avatar
    Ravid Paldi

    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.