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

[green]

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?

[rapidbisupport · Accepted solution]

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]

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]

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

[Benji_PaldiTeam]

Hi @green ,

We developed the sophisticated yet free plugin "Advanced Default Values" to handle these exact cases.

Feel free to contact us and get the plugin or if you have further questions, we're always happy to help (: 

[email protected]

Paldi Solutions - Number #1 Sisense Plugins Developer 

[green]

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?