Table aggregation by date

Elastic Stack Kibana
1

Greetings all, a noob question maybe, but haven't found a solution on Github or forum. Simillar question was asked here, but related to graphs-

Use case:
I want to build a visualization (table) that will include websites, sales count, aggregated by day like in the following example:

table-example.JPG999×169 36.9 KB

But in Kibana I cannot aggregate by day view, as it only can be aggregated by metrics such as count, avg, sum, etc.

What I want to achieve is a result in a abovementioned screenshot, but for table I get only this view

Visualization-table-kibana.png1880×864 43.7 KB

I would appreciate any hints
Best,

1 Like
2

Hi Arthur,

It doesn't look like Kibana has this capability right now. The only way I found I could get something like this was to export the data from Kibana and create an Excel pivot table on that data. Is that what you already did? I could describe the steps I did if you need it.

It looks like what you're asking for is in this issue; https://github.com/elastic/kibana/issues/3620

You could add your comments there.

Thanks,
Lee

3

Thanks for the reply, the example above is actually the process we follow to process data separately in Excel, as Kibana does not have that capability.
We can of course use graph visualization, as X axis can be used for date histogram, but indeed, this solution cannot be applied to tables.

As far as I understand there is no workaround to use the input JSON parameters like : aggregate by {date} ?

4

Hi Arthur,

Just a question, couldn't you just use a sum metric aggregation for the field you want. It would sum up the sales field, for each day. That is, if the sales field is a number, all the sales field values for each document that falls into that day bucket for that website will be summed to get a total sales count. As far as I understand it.

Would that work?

5

Hi, not quite sure I got it. The thing is that in metric aggregation I use simple count (not by field), as each sale is a line, so count would retrieve the general number of sales. Then by splitting rows by terms I can select the field (in which case is the website) so I can see total amount sales for each website for a selected timeframe (used timepicker above). But the table does not allow to represent date as in my example from Excel, + each row actually contains a timestamp, so the only way to view the performance by selected period is to use timepicker

6

Hi Arthur,

I am not sure I understand either.

Is sale a field in each document, where sale represents the number of sales, like so:

{
    sale: 12
}

Or, does a document constitute a sale, where each document represents 1 sale?

If its the former, then you can use a sum aggregation on the sales field to get a total count for each website per day. You would need to do a terms aggregation on each website and then a sub aggregation (date histogram) with a daily interval. That will give you a table with websites, timestamps (per day), and total count as the column fields.

If you wanted to break this out, you could do a split table first on the website, and then a split row on timestamp with the sum (metric) aggregation on the sales field, and that will give you a table for each website with timestamp and total count as the columns for each table.

However, if its the latter, I am not sure you can get what you are looking for.

7

Thanks for spending your time Shelby!
it is actually the latter case, so the first approach won't work as a sale
is not a field but merely one document.
What can be done is in this example, found by my colleague yesterday -
http://monosnap.com/image/4xfv1hzpHdjlnHQqz28I5FwuOzOgu6, at least it is
closer to what we are looking for.