Multiple time range filters

[itziarmj]

In Superset 2.1.0 is possible to have multiple time-based filters but, as noted in https://preset.io/blog/exploring-trends-over-non-temporal-dimensions-in-superset-and-preset/#3-exploring-charts-in-dashboards, in the Dashboard there is only one time range for all of them.
Is it foreseen to have different time ranges for each of the time-based filters?
In our dashboards we have several time ranges to filter, like manufacturing date, delivery date... and it makes more sense to be able to filter them with different time ranges.
This was proposed also in #23842

[dijkstra2003]

Any updates on this? We would really like it and i'm considering building it myself

[sfirke]

I don't believe this has moved. @dijkstra2003 would you be interested in contributing it as a feature back to the main Superset project?

[dijkstra2003]

If i'm building this i will definitely contribute and give the code to the main superset ;)

[goto-loop]

This would be a very useful feature. Another use case for it is a side-by-side comparison: having the same chart twice in one dashboard and applying a different time range to each.

[phyndman]

you can do this now by just applying each time filter to specific charts/panels using the "scoping" on the filter :-)

[Stefan781]

+1. Right now, if you add two time range filters, the second one seems to overwrites the first, which makes it impossible to recover its value even when using the filter variable with Jinja.

[phyndman]

Don't suppose anyone found some clever workaround to inject 2 different "Time Ranges" using jinja into queries?

[axoxymous-coder]

Has anyone found / know any workaround on ways to have atleast two time-range filters in a dashboard? One of our use case is getting blocked since we need to filter on two date ranges ...

[phyndman]

Yeh, essentially I've got a "date dimension" dataset and I've added 4 new columns to it (which are essentially replicas) that the filters get their values from so people can select them from a drop down. Not the most elegant thing but does what it says on the tin for the end users!

[Stefan781]

So are you limited to some predefined dates or did you come up with a workaround to let the user choose any date?

[phyndman]

User can choose any dates they want as essentially they get presented with a list of all dates in the 5 years prior to "today" in all 4 I've created and can select any date they want in each. They need to know that Start needs to be before End or else the query will be a bit strange however or may not work properly. With 2 proper time range filters they don't have to search for dates they could use the inbuilt functions on dates (last week, current month) so would just be a much easier user experience and better for them.

[Stefan781]

Aha, I see! That's a neat solution if the user doesn't mind scrolling to find their date. Thanks for sharing!
Edit: Just realized, that they don't have to scroll but they can also search for the value.

[phyndman]

Yeh lots of our use cases are looking at comparing any user defined "time range A" vs "time range B" in the same chart table and showing a variance. They can also apply more filters etc. Having to search though a massive list of dates is not ideal but gets to some sort of solution. If I were an awesome coder I'd have a go at being able to properly pull 2 time range filters into Jinja (currently you just get the one of them) but fear I'd bring down the entire project!!

[michael-s-molina]

Hi folks. I'm working on a SIP with @john-bodley and @kasiazjc to improve time filters that will resolve this problem. Just to set expectations, we are currently focused on delivering 4.0, but we should continue the SIP work after the release.

[phyndman]

no worries thank you for the update. keep up the good work :-)

[Hatake07]

Hi @michael-s-molina , is there any public document on which we could take notes about roadmap milestones for 2025?

[michael-s-molina]

We generally use the SIP board to track the major epics that are happening. Currently, there's no one working on improving time filters.

[ichsan-data]

hi @michael-s-molina , is there anyupdate?

[michael-s-molina]

Hi @ichsan-data. This project was deprioritized in favor of Superset extensions. There's no current plan to tackle this in the short term.

[itziarmj]

We also have these types of cases. We have two workarounds: When we need “flexible” dates, we let the user input the date as a string in a defined format. Pe 2024-01-01 When we have few options, we have a table with the list of possible dates to be selected. From: phyndman ***@***.***> Sent: Friday, February 16, 2024 12:05 PM To: apache/superset ***@***.***> Cc: Itziar Martínez ***@***.***>; Author ***@***.***> Subject: Re: [apache/superset] Multiple time range filters (Discussion #24165) Yeh lots of our use cases are looking at comparing any user defined "time range A" vs "time range B" in the same chart table and showing a variance. They can also apply more filters etc. Having to search though a massive list of dates is not ideal but gets to some sort of solution. If I were an awesome coder I'd have a go at being able to properly pull 2 time range filters into Jinja (currently you just get the one of them) but fear I'd bring down the entire project!! — Reply to this email directly, view it on GitHub<#24165 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AYFVXNYILOZJ5IFQY7Y5PQ3YT44OBAVCNFSM6AAAAAAYJ6ZUKGVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DIOJRGEZDE>. You are receiving this because you authored the thread.Message ID: ***@***.******@***.***>>

[seandiacono]

+1, this would be super handy for us to be able to filter multiple time based attributes at the same time

[dtes]

I found that API can receive multiple time range filters. Similar to number range filters I send time ranges. Example of request I sent in my dashboard.

POST /api/v1/chart/data?form_data=%7B%22slice_id%22%3A95%7D&dashboard_id=114 HTTP/1.1
Host: superset.myapp.kz
Cookie: session=.eJyNVVuvozYQ_itVnruVbS4J-8YhgcABsuGOqyoCG8I1cBJIAqv-95qcbbVqX_qA
Content-Type: application/json
Authorization: Bearer eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiJhZG1pbiIsIm9yaWdprO98UA
Content-Length: 5949

{
    "datasource": {
      "id": 1,
      "type": "table"
    },
    "force": false,
    "queries": [
      {
        
        "filters": [
          {
            "col": "doc_category_id",
            "op": ">=",
            "val": 0
          },
          {
            "col": "doccreatedata_dtcreate",
            "op": ">=",
            "val": "2024-12-08T00:00:00"
          },
          {
            "col": "doccreatedata_dtcreate",
            "op": "<=",
            "val": "2024-12-10T00:00:00"
          },
          {
            "col": "regdata_regdt",
            "op": ">=",
            "val": "2024-12-10T00:00:00"
          },
          {
            "col": "regdata_regdt",
            "op": "<=",
            "val": "2024-12-13T00:00:00"
          },
          {
            "col": "doccreatedata_dtcreate",
            "op": "TEMPORAL_RANGE",
            "val": "No filter"
          },
          {
            "col": "doc_type_name",
            "op": "IN",
            "val": [
              "IncomeDoc"
            ]
          },
          {
            "col": "regdata_regdt",
            "op": "TEMPORAL_RANGE",
            "val": "No filter"
          }
        ],
        "extras": {
          "time_grain_sqla": "P1D",
          "having": "",
          "where": ""
        },
        "applied_time_extras": {},
        "columns": [
          {
            "expressionType": "SQL",
            "label": "Ссылка",
            "sqlExpression": "CONCAT('<a href=\"', 'https://myapp.kz/app/view/', id,'\" target=\"_blank\">Посмотреть документ</a>')"
          },
          {
            "expressionType": "SQL",
            "label": "Номер документа",
            "sqlExpression": "doc_num_calc || ''"
          },
          {
            "expressionType": "SQL",
            "label": "Тип документа",
            "sqlExpression": "controldata_controltype_labelru || ''"
          },
          "doc_type_labelru",
          "regdata_regdt",
          {
            "expressionType": "SQL",
            "label": "Рег дата или дата создания",
            "sqlExpression": "case \nwhen regdata_regdt is not null\nthen regdata_regdt\nwhen regdata_regdt is  null\nthen doccreatedata_dtcreate\nend"
          }
        ],
        "orderby": [],
        "annotation_layers": [],
        "row_limit": 1000,
        "series_limit": 0,
        "order_desc": true,
        "url_params": {},
        "custom_params": {},
        "custom_form_data": {},
        "post_processing": []
      }
    ],
    "form_data": {
      "datasource": "1__table",
      "viz_type": "table",
      "slice_id": 95,
      "query_mode": "raw",
      "groupby": [],
      "time_grain_sqla": "P1D",
      "temporal_columns_lookup": {
        "regdata_regdt": true,
        "controldata_initialduedt": true,
        "controldata_duedt": true,
        "controldata_controlenddt": true,
        "paymentdata_paymentregisterdt": true,
        "doccreatedata_dtcreate": true
      },
      "all_columns": [
        {
          "expressionType": "SQL",
          "label": "Ссылка",
          "sqlExpression": "CONCAT('<a href=\"', 'https://myapp.kz/app/view/', id,'\" target=\"_blank\">Посмотреть документ</a>')"
        },
        {
          "expressionType": "SQL",
          "label": "Номер документа",
          "sqlExpression": "doc_num_calc || ''"
        },
        {
          "expressionType": "SQL",
          "label": "Тип документа",
          "sqlExpression": "controldata_controltype_labelru || ''"
        },
        "doc_type_labelru",
        "regdata_regdt",
        {
          "expressionType": "SQL",
          "label": "Рег дата или дата создания",
          "sqlExpression": "case \nwhen regdata_regdt is not null\nthen regdata_regdt\nwhen regdata_regdt is  null\nthen doccreatedata_dtcreate\nend"
        }
      ],
      "percent_metrics": [],
      "adhoc_filters": [
        {
          "expressionType": "SIMPLE",
          "subject": "doccreatedata_dtcreate",
          "operator": "TEMPORAL_RANGE",
          "comparator": "No filter",
          "clause": "WHERE",
          "sqlExpression": null,
          "isExtra": false,
          "isNew": false,
          "datasourceWarning": false,
          "filterOptionName": "filter_ch5hs924zt6_qrxq3tj3asj"
        },
        {
          "expressionType": "SIMPLE",
          "subject": "doc_type_name",
          "operator": "IN",
          "operatorId": "IN",
          "comparator": [
            "IncomeDoc"
          ],
          "clause": "WHERE",
          "sqlExpression": null,
          "isExtra": false,
          "isNew": false,
          "datasourceWarning": false,
          "filterOptionName": "filter_v3k42b054eh_ezigirufv3w"
        },
        {
          "expressionType": "SIMPLE",
          "subject": "regdata_regdt",
          "operator": "TEMPORAL_RANGE",
          "operatorId": "TEMPORAL_RANGE",
          "comparator": "No filter",
          "clause": "WHERE",
          "sqlExpression": null,
          "isExtra": false,
          "isNew": false,
          "datasourceWarning": false,
          "filterOptionName": "filter_wbpeaftqyab_b8l5kxul8bv"
        }
      ],
      "order_by_cols": [],
      "row_limit": 1000,
      "server_page_length": 10,
      "order_desc": true,
      "table_timestamp_format": "smart_date",
      "show_cell_bars": true,
      "color_pn": true,
      "extra_form_data": {
        "filters": [
          {
            "col": "doc_category_id",
            "op": ">=",
            "val": 29
          }
        ],
        "time_range": "2024-12-08T00:00:00 : 2024-12-12T00:00:00"
      },
      "dashboards": [
        77
      ],
      "label_colors": {},
      "shared_label_colors": {},
      "extra_filters": [],
      "dashboardId": 114,
      "force": false,
      "result_format": "json",
      "result_type": "full",
      "include_time": false
    },
    "result_format": "json",
    "result_type": "full"
  }

See how I send time range filters:

          {
            "col": "doccreatedata_dtcreate",
            "op": ">=",
            "val": "2024-12-08T00:00:00"
          },
          {
            "col": "doccreatedata_dtcreate",
            "op": "<=",
            "val": "2024-12-10T00:00:00"
          },
          {
            "col": "regdata_regdt",
            "op": ">=",
            "val": "2024-12-10T00:00:00"
          },
          {
            "col": "regdata_regdt",
            "op": "<=",
            "val": "2024-12-13T00:00:00"
          }

In response, you can get a generated SQL query, and in my case, this is the correct query.

So, we need to add custom time range filters to the superset frontend and chose it in the dashboard filters.

[marianysilva]

Use case:

• Date Range: Last Week

• Chart A:

  • Dataset: 1
  • Column X in Date Range filter

• Chart B:

  • Dataset: 1
  • Column Y in Date Range filter

• Details Table:

  • Dataset: 1
  • Column X OR Column Y in Date Range filter