Canvas dropdownfilter does not filter

[JeroenK (Jeroen de Koning)]

I'm working on my first Canvas and want to show statistics about my payment brands in my shop, including a time filter and a payment brand filter:

Everything works as I want, except for the payment brands filter. After some fiddling, I did get to populate it correctly but when I select one of the payment brands, the chart is not filtered based on that brand (which is my goal) and throws an error (that is gone very quickly, so I had to record my actions to be able to see what it says

My code for the chart:

filters
| timelion 
  query=".es(index=wlsips-demo, timefield='@timestamp', metric='count:transaction_amount', split=payment_brand_name.keyword:10).label(\"$1\", \"^.*keyword:(\\S+).*\")" interval="1d"
| pointseries x="@timestamp" y="value" color="label"
| plot defaultStyle={seriesStyle fill="1" lines="3"} 
  palette={palette "#44697D" "#5E2D61" "#739ABC" "#72CE98" "#4B92DB" "#BF2296" "#949D9E" "#5E6A71" "#AF94A3" gradient=false} legend="nw" 
  font={font family="'Myriad Pro', 'Cambria', sans-serif" size=12 align="left" color="#000099" weight=600 underline=false italic=false}
| render 
  containerStyle={containerStyle backgroundColor="#FFFFFF" border="1px solid #BBBBBB" borderRadius="4px" padding="10px" opacity="1"}

My code for the dropdown

filters
| timelion 
  query=".es(index=wlsips-demo, timefield='@timestamp', metric='count:transaction_amount', split=payment_brand_name.keyword:10).label(\"$1\", \"^.*keyword:(\\S+).*\")" interval="1d"
| mapColumn "brands" fn={getCell "label"}
| columns include="brands"
| dropdownControl valueColumn="brands" filterColumn="brands"
| render

The error I'm getting:

Anyone any idea how to fix this?

Thanks

Jeroen

[Joe_Fleming]

when I select one of the payment brands, the chart is not filtered based on that brand (which is my goal) and throws an error

I think what's happening is that you don't have any rows by the time you use mapColumn in the filter element's expression. It's a terrible behavior, and a bug for sure. Anyway, I think you have a column name mis-match.

I think the issue is with dropdownControl valueColumn="brands" filterColumn="brands". valueColumn is the column name in the datatable that gets passed into that function that you want to use for the values in the dropdown, and in your case the is correct (you see values in the dropdown). But filterColumn informs the data source function (timelion in your case) to try to filter on that field, and I suspect your data in Elasticsearch doesn't actually have a brands field. So what happens is that applying the filter causes the timelion query to return 0 rows, which means that a datatable with 0 rows is passed into mapColumn, and because of a bug in that function, you get an error (instead of an empty dataset).

You can test this out yourself, add a new element that only has this as its expression:

filters
| timelion 
  query=".es(index=wlsips-demo, timefield='@timestamp', metric='count:transaction_amount', split=payment_brand_name.keyword:10).label(\"$1\", \"^.*keyword:(\\S+).*\")" interval="1d"

That should generate a table element with all the data you'd expect to see. Now try to apply a filter using the existing filter element. The filter element should become an error like it does now, and you should see an empty table in that element you just added.

To fix this, you need to change the filterColumn value in the mapColumn function so that it points to a valid field in your data. Then applying the filter will map to a valid field, and the timelion function should return some data and things will work.

I can't quite make sense of where the payment-brand in the error message is coming from though... knowing your data structure might help figure that one out, if you can share that. Only the field names in the documents in Elasticsearch are important to know.

Also, FYI, you probably don't want to use the filter function on the filter element, otherwise once you apply a filter, it'll be the only thing that shows up in the dropdown.

[JeroenK (Jeroen de Koning)]

Ah, thanks for the help, it's working now!

• Adding the data table, helps a lot with seeing what's going on
• Although in the data table the payment brands are in the column 'label', eventually I needed to use the actual field name from the index filterColumn="payment_brand_name"
• And removed the 'filters' function on the filter element as you advised (prevents a lot of headaches)

If it's still interesting, I also included my index mapping (had to garble a few fields though):

{
  "transactions" : {
    "mappings" : {
      "doc" : {
        "properties" : {
          "@timestamp" : {
            "type" : "date"
          },
          "@version" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "reference_id" : {
            "type" : "text"
          },
          "end_dtime" : {
            "type" : "date"
          },
          "host" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "message" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "unit_id" : {
            "type" : "keyword"
          },
          "contract_id" : {
            "type" : "keyword"
          },
          "path" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "payment_brand_name" : {
            "type" : "keyword"
          },
          "field2" : {
            "type" : "integer"
          },
          "field3" : {
            "type" : "text"
          },
          "reconciled" : {
            "type" : "boolean"
          },
          "reconciled_ref" : {
            "type" : "text"
          },
          "settlement_amount" : {
            "type" : "float"
          },
          "settlement_amount_curr_iso" : {
            "type" : "keyword"
          },
          "settlement_reference" : {
            "type" : "text"
          },
          "field4" : {
            "type" : "text"
          },
          "start_dtime" : {
            "type" : "date"
          },
          "transaction_amount" : {
            "type" : "float"
          },
          "transaction_amount_curr_iso" : {
            "type" : "keyword"
          },
          "field5" : {
            "type" : "text"
          },
          "transaction_status_name" : {
            "type" : "keyword"
          },
          "field6" : {
            "type" : "keyword"
          },
          "field7" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          }
        }
      }
    }
  }
}

[JeroenK (Jeroen de Koning)]

BTW, would it also be possible to:

1. Have a dropdownfilter or other element to switch the line chart from a count metric to a sum metric?
2. create 'buttons' that have a single function such as the default date filters of 7 days and 30 days or the switch from count metric to sum metric

Just thinking aloud, there is a real business case to use canvas in one of our projects and it's still a bit hard to understand the possibilities (and the impossibilities).

[Joe_Fleming]

would it also be possible to have a dropdownfilter or other element to switch the line chart from a count metric to a sum metric?

Not currently, though we've talked a lot about adding conditional elements, so you could control what ends up on the workpad based on some condition (data or whatever else). Feel free to open an issue to track the idea on the Kibana issue tracker, it doesn't look like there is one.

would it also be possible to create 'buttons' that have a single function such as the default date filters of 7 days and 30 days or the switch from count metric to sum metric

You could write your own plugin that added such a control, it just needs to render some control and have a client handler that sets a filter, which would be hard to make. There's nothing that comes with Canvas that does that, but that's an interesting idea for a new type of filter we could add. Feel free to open an issue for that as well.

Canvas gives you a lot of power in the expression language, but it's also extremely pluggable, so don't be afraid to write you own functions, renderers, elements, or anything else. We're still working on docs for that stuff, but you can use the code in the repo as examples of how to create them. And if you're interested in doing it, I can provide some guidance to get you started.

Although in the data table the payment brands are in the column 'label', eventually I needed to use the actual field name from the index filterColumn="payment_brand_name"

I think there's a way in the Timelion syntax to rename the label, but I don't really know that syntax so I could be wrong.

You can do it in the expression though, using the alterColumn function. After you use the timelion function, add this: | alterColumn column=label name=payment_brand_name.

That will rename the column name of the data to payment_brand_name. Note that you'll have to update the column name in following functions if you're using that data (things pointseries and ply will be affected).

You can use any string you want in there too, just add quotes if you use spaces, like so: | alterColumn column=label name="Payment Brand". You can also do this multiple times to change any or all of the other column names to something that's nicer to read.

[JeroenK (Jeroen de Koning)]

Feature request created: https://github.com/elastic/kibana/issues/28151

In the end I think it's quite logical how things work with the dropdown. First of all I have this line chart:

| timelion 
  query=".es(index=wlsips-transactions, timefield='@timestamp', metric='count:transaction_amount', split=payment_brand_name:10).label(\"$1\", \"^.*_name:(\\S+).*\")" interval="1d"
| pointseries x="@timestamp" y="value" color="label"
| plot defaultStyle={seriesStyle fill="0" lines="2"} 

As I understand it this results in a table with a count for each day and for each unique payment_brand_name. It plots the numbers against the dates in the line chart, each line is one payment_brand_name.

Now the filter uses the same data to populate it.

timelion 
  query=".es(index=wlsips-transactions, timefield='@timestamp', metric='count:transaction_amount', split=payment_brand_name:10).label(\"$1\", \"^.*_name:(\\S+).*\")" interval="1d"
| mapColumn "brands" fn={getCell "label"}
| columns include="brands"
| dropdownControl valueColumn="brands" filterColumn="payment_brand_name"

So, the labels resulting from the timelion query are mapped in a new column brands. That list is used to show in the valueColumn. The filterColumn refers to the chart and since each line in the chart is represented by a payment_brand_name, the dropdownfilter should target that.

I more or less see it as a where clause for the timelion query of the chart ..... where payment_brand_name = VISA.

Thanks for all the help and I will try to dive into the options to write plugins, hopefully more docs are coming soon!