Canvas line chart with timeseries

#1

Using Canvas on Kibana version 6.6.0. I'm new to canvas and struggling to display a line chart with counts over time. Below is the query I'm using. The time doesn't update whenever I change the time filter. I have the time filter using click_time as its timestamp.

SELECT HISTOGRAM("click_time", INTERVAL 1 hour) as dd, count(click_id)
, count(distinct member_id)
, count(distinct project_code)
, SUM(revenue)/count(distinct click_id)*100 as epc
, SUM(revenue) as rev

FROM "mr_monitor*"
WHERE customer='Prodege'
group by dd

No matter if I select last 30 days, 60 days, or 24 hours the time frame in the line chart always shows last 2-3 days.

Any help is appreciated!

#2

Hi @dave.b

Could you share the full expression that you are using for the line chart? If you select the line chart, there should be an "Expression Editor" link in the bottom right corner where you can see this expression.

#3

Thanks for the response....

filters
| essql
query="SELECT HISTOGRAM("click_time", INTERVAL 1 hour) as dd, count(click_id)
, count(distinct member_id)
, count(distinct project_code)
, SUM(revenue)/count(distinct click_id)*100 as epc
, SUM(revenue) as rev

FROM "mr_monitor*"
WHERE customer='First'
group by dd
"
| mapColumn name="formatted_dd" fn={getCell "dd" | formatdate "M/D"}
| pointseries x="dd" y="COUNT_click_id_"
| plot defaultStyle={seriesStyle lines=3} yaxis=true
| render containerStyle={containerStyle}

#4

Nothing immediately jumps out at me as to what could be causing this based on that expression.

Couple of things to check.

  1. If you remove everything from the expression after the query, does the resulting table have the expected date-filtered data in it?
  2. If you delete everything and start over, does the issue remain? Canvas was in a beta until 6.8, and I know there were some issues with some filtering in some of those earlier releases.