How to filter dashboard by field maximum value

Hi all.

This seems easy enough, but I'm being unable to achieve it, and don't find an answer on internet.

In our use case, we've built a dashboard with several lens graphs. Users reach that dashboard through a shared link where we include some filter parameters in a querystring, so, depending where they click, they reach the dashboard pre-filtered by their click selection.

Now, we want to add to our querystring a new parameter to filter data by default for the last year. We have a field with the year on our index, and we just need to add a filter on the querystring to filter by this year field maximum value.

We cannot use a date filter because yearly data are loaded mid year more or less, so we want that while data from 2025 is not available, users reach the dashboard automatically filtered by 2024, and the moment 2025 data is available, they reach it filtered by 2025.

I'm struggling to achieve this simple filter directly on the dashboard, previously to transfer it to the querystring that we add to the links

1 Like

Hi @PMF,

To confirm, is the year being passed via the query string parameter on the URL? Or are you wanting to calculate the last year and they pass it through? Is this an embedded dashboard or a direct link that users click on to get to the dashboard?

Hi Carly.

Thanks for replying. The idea is to embed the filter in the querystring, but we don't know the desired value on the moment we create the link, we need to somehow calculate it or insert a function that calculates it (max(year)?) inside the querystring.

I will explain to you our use case:

We work for the town council, and have a GIS with geographical data of the city. Users that access to those maps can click over every different neighborhood and get redirected to a elastic dashboard where population statistical data are showed, the link filters that data in advance by the neighborhood where users have clicked. Now we want to add to that filtered links something that allows us to assure that users see the lattest data available (those statistical data loads yearly).

Please, don't hesitate in asking for more information if what we are trying is not clear enough.

Hello @PMF

Until we wait for response from Carly, i do not see how we can dynamically update the dashboard with the latest year filter.

As per me below is 1 work around , what if we add a filter on field year & the user will be able to see the available options of year for the dashboard. So if the year 2025 data is available they will see that value in the slider (min-max). From this slider they can set the maximum value?

Thanks!!

Welcome to the topic, Tortoise, and thank you for your idea.

Sadly, we already have a filtering component on the dashboard by year. But we've seen that users tend to miss new data when it's suddenly available, and they tend to stick whith the default filtered status, whatever it will be. This is the reason why we are looking for alternatives.

1 Like

Yeah @Tortoise's suggestion to include a filter for the year value based on the field is probably the easiest option. You can then specify the value in the URL using the Kibana query parameters as discussed here.

If you want the value to be completely dynamically generated this would be easy if you were embedding the dashboard as you could probably have logic in JavaScript to generate the year.

Do I assume, then, that there is no way to query a field by its maximum value?

You could use a max aggregation on a runtime field:

Thank you, this seems promising, probably we'll be able to stablish a runtime field containing the maximum year value and querying it.

Maybe I am being slow today, but how will this work?

Whats going to be the filter? The docs have the example below, the runtime field is price.adjusted and that is aggregated into max_price. The runtime field is just a function from the individual document.

POST /sales/_search
{
  "size": 0,
  "runtime_mappings": {
    "price.adjusted": {
      "type": "double",
      "script": """
        double price = doc['price'].value;
        if (doc['promoted'].value) {
          price *= 0.8;
        }
        emit(price);
      """
    }
  },
  "aggs": {
    "max_price": {
      "max": { "field": "price.adjusted" }
    }
  }
}

How precisely?

Hi Kevin, welcome to the topic.

I'm trying to figure it right now, but I'm thinking that if I can stablish an aggregation like the max_price of your example, I should be able to query through the url with something like:

https://.....?_g=(filters:!((query:(match_phrase:(price.adjusted:max_price)))))

Understood and good luck. But please let us know if it works. Great if it does, it'll add a new tool to the toolbox. But I am dubious, though I hope I am also wrong to be dubious.

1 Like

And? Were you able to get it to work ?

Sadly not yet. I'd to turn to other matters before exhausting every possibility I had in mind. So... I didn't threw the towel yet with this, and will probably pass some time before I can spend time on it. But not fear, if I finally manage to get it to work I'll post the result here.