Canvas does not recognize Date field

[MartinVarbanov]

I am running canvas for Kibana 6.2.3. There is a date field "Activity month" which is working fine if I build Bar charts in Kibana. However Canvas does not recognize "Activity month" as a date filed and I cannot build visualization correctly. Any suggestion how to resolve this issue?

[bhavyarm]

@rashid / @Joe_Fleming thanks!

[Joe_Fleming]

What type of field does Canvas think it is? And what's the mapping for the field (you can check in Kibana, or use the mapping api)?

I also wonder if perhaps the format on the date field is different than what Canvas is expecting somehow. If you could just use the esdocs function to get some data and post a screenshot of the table output, that would be helpful too.

[MartinVarbanov]

I am not using special mapping. The data is loaded with logstash using
stdout { codec => json_lines }

According to the coordinate plot it seems the "activity month" is a float between -1 and 1.
I am using the following code:

filters | esdocs index="wbs*" | pointseries x="activity month" y="sum(total_activity_hours)" color="hml" | plot defaultStyle={seriesStyle points=5} | render

Kibana shows:

[MartinVarbanov]

The format of the "activity month" is the same as the "@timestamp" but if I build coordinate plot with the "@timestamp" is it working correctly vs. the "activity month".

Example of single document:
{
"_index": "wbs",
"_type": "doc",
"_id": "UHFftmIBjBO9JUeJhxsK",
"_score": 1,
"_source": {
"activity month": "2018-03-01T00:00:00.000Z",
"total_activity_hours": 8,
"hml": "High",
"@timestamp": "2018-04-11T20:22:16.130Z"
},
"fields": {
"activity month": [
"2018-03-01T00:00:00.000Z"
],
"@timestamp": [
"2018-04-11T20:22:16.130Z"
]
}
}

[Joe_Fleming]

I figure out the issue. There's a problem in Canvas where it doesn't handle field names with spaces correctly. I'll get a fix in, and hopefully ship it in the next version.

In the meantime, you can use alterColumn to rename the column, like so:

filters | esdocs index="wbs*" 
| alterColumn "activity month" name=activity_month
| pointseries x=activity_month y="sum(total_activity_hours)" color="hml" 
| plot defaultStyle={seriesStyle points=5} 
| render

You can see the issue if you break the expression down:

Here, I've split up each of the parts of the expression, starting with the data, then just the pointseries, and finally the whole thing with plot. Notice how the column for the pointseries output has a question mark. This means that it doesn't know what type of data is in there, and when plot tries to use it, it ends up treating the value as a number, resulting in 0... kind of.

When you rename the column you get a different outcome:

With the space removed from the column name, pointseries now correctly assigns the output type as a string, and plot knows how to correctly handle the data.

You can go even further and cast the value into an actual month name, using mapColumn:

filters | esdocs index="wbs*" 
| alterColumn "activity month" name=activity_month
| mapColumn activity_month expression=${getCell activity_month | formatdate "MMM"}
| pointseries x=activity_month y="sum(total_activity_hours)" color="hml" 
| plot defaultStyle={seriesStyle points=5} 
| render

Hope that's all helpful!