Forum Discussion

green's avatar
green
Cloud Apps
12-19-2023
Solved

Date filters - possible to have fixed start date, lagged end date?

We have certain calendar-based metrics that need to be lagged (typically by 15 or 30 days).  For instance YTD 2023, but excluding the last 15 days.   

I haven't been able to figure out how to do this within the basic filter selectors.  It's almost like I need a hybrid option where I can use the fixed calendar to select a start date, but the end date as "today minus x days".

Right now my only workaround is to select a fixed end date, and then update it manually periodically.  Am I missing something that would allow me to do this properly?

Dashboards & Reporting

5 Replies

Replies have been turned off for this discussion
  • rapidbisupport's avatar
    rapidbisupport
    Data Pipeline
    12-20-2023

    Hi green ,

    I would first duplicate my date column in the data model, and then set a custom filter against 'days in duplicated date':

     

    {
  "explicit": false,
  "multiSelection": true,
  "exclude": {
    "last": {
      "count": 15,
      "offset": 0
    }
  }
}

     

     This will filter for days in duplicated date where the days aren't the last 15. You could increase 'count' to exclude more, or manipulate offset to do some really fancy stuff like 'exclude 10 days from 2 days ago'.

    You can then add the original date filter and use it in conjunction with this one.

    https://sisense.dev/guides/querying/jaqlSyntax/#filtering

     

    Let me know if this works for you?

    Thanks,

    Daniel

    RAPID BI

    [email protected]

    RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons

     

     

    • green's avatar
      green
      Cloud Apps
      12-20-2023

      This makes sense.  I hate that it may require a 2nd date field and multiple filters, but I can see that this would definitely accomplish what I need, and it may be my best option.  Thanks for the idea!

      • rapidbisupport's avatar
        rapidbisupport
        Data Pipeline
        12-21-2023

        Agreed, it feels wrong to need the duplicated date column.

        It's possible to achieve this with a single OOTB (out of the box) custom date filter, but you'd have to compromise and remove the user friendly 'make a selection with the calendar filter'.

        The single custom filter would look like this:

         

        {
  "and": [
    {
      "exclude": {
        "last": {
          "count": 15,
          "offset": 0
        }
      }
    },
    {
      "from": "2023-11-30",
      "to": "2023-12-30"
    }
  ]
}

         

        Honestly, I think the best solution for your viewers would be the second date filter, but then using the dashboard script below to hide the 'last 15 days' filter from the filters panel:

         

        const filterToHide = 'Days in date2'

dashboard.on('domready', (d, args) => {
	$('.f-header-host').filter((i, elem) => { return elem.innerText === filterToHide }).parent().parent().hide()
})

         

        Let me know how you go?

        Thanks,

        Daniel

        RAPID BI

        [email protected]

        RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons

    • green's avatar
      green
      Cloud Apps
      12-20-2023

      I followed the link to the plug-in, but the page really doesn't offer any description of what the plug-in does.  Can you provide more detail about how it meets this particular use case?