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 thi...
dashboards & reporting
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