Breakdown of Time Entry

[schoffelman (Tim Schoffelman)]

I'm working on project where I have time sheet entries from every employee. Every doc includes the amount logged, their name, and a category id. From those category id's we section it into 3 major groupings.

Does anyone have any ideas on graphing out on a weekly or monthly basis what the company average is from the average of each employee, based off of those three major categories?

If it was a bar graph, I'd love to have the X axis the date (weekly if possible), the Y axis those three categories, and then the bar itself divided among those three main categories.

[tsullivan (Tim Sullivan)]

Having a bunch of averages, and taking the average of those unfortunately does not give you meaningful data. That is because averages are only comparable to each other if they are based on the same denominator.

Is an average of 6 "more" than an average of 5? If the average of 6 came from [12, 0] and the average of 5 came from 8 million records that were all 5s, is the 6 still higher? You have to know more about how those initial averages were calculated. In this case, one of the avgs was calculated with 2 as the denominator and the other was 8 million.

That's an extreme difference example, but I hope it shows the point: if on the one hand, 2 things happened and on the other hand, 8 million things happened, just knowing that some numbers came out which were 6 and 5 is a complete disconnect.

I'd say a way to come up with some useful information where the integrity of the information is the same would be to come up with a way to normalize an average of something you're interested in (multiply the numerators so the denominator can be the same), add up all the numerators and divide that by the common denominator. That gives you a normalized average, and that is what you would want the visualization to be working off of.

[schoffelman (Tim Schoffelman)]

Good point Tim. I got so focused on the how, that I completely forgot to take a step back and see that my math was off. Thank you for the suggestions.

The larger point I probably failed at communicating was a way to see the companies averages within these categories based on a timeline. Thank you for your suggestions.

[tsullivan (Tim Sullivan)]

Hi, I got to thinking, maybe it would help to see this data as a bar chart with a date histogram (maybe 1 week interval), with split series. The Y-axis is the overall average of amount. Now, split the bar by terms, which can be employee name or category ID.

I have some sample data log I can use to illustrate it, where the documents have "partition", "metric" , and "success" fields. Example document:

{
  "_index": "partitions",
  "_type": "doc",
  "_id": "xrxHg2gBmskfQiRIKwuQ",
  "_version": 1,
  "_score": null,
  "_source": {
    "@date": "2019-01-25T04:30:45Z",
    "partition": "/private/var/vm",
    "metric": 6852,
    "success": false
  },
  "fields": {
    "@date": [
      "2019-01-25T04:30:45.000Z"
    ]
  },
  "sort": [
    1548390645000
  ]
}

Without splitting any series, I can get an overall average of "metric" per week:

Now I can split the series on a terms aggregation and use "partition" as the field to split by. Now I have average per partition per week, for each of the top 5 partitions of each week:

I can also break it down further and aggregate by the success true/false values. But at this point I don't want to add another aggregation that will segment these bars, because I'd end up with the same partitions showing twice in a bar. Instead, I want to split the entire chart into 2 charts and have 1 for success:true and forsuccess:false`.

Here's my Data tab now:

If I change some options in "Metrics & Axes" I can make the chart type as "area", the line mode as "smooth" and the mode as "wiggle". Why all that stuff? It kind of allows me to make a chart similar to the famous "Ebb and Flow of Movies" chart: https://archive.nytimes.com/screenshots/www.nytimes.com/interactive/2008/02/23/movies/20080223_REVENUE_GRAPHIC.jpg

Now here's the chart:

I split the data up by success which gives me 2 charts because a boolean can have one of 2 values. The top one is labeled 1: success: Descending so that's an aggregation of the data where success:true If I used a filters aggregation instead of a terms aggregation for success, that aggregation in the editor would look like this:

And now my legends look like this:

If this is starting to look good, I'd urge you to keep looking at other tools in Kibana such as Visual Builder, Timelion, and Canvas: those can each do the same kinds of aggregations I've shown you but offer different options on customizing the presentation.

The idea here is to have the averages split by employee (what I did with partition), and then the charts split by category (what I did with success).

[schoffelman (Tim Schoffelman)]

Thanks @tsullivan - I took your suggestions and was able to get it figured out. Here's some screenshots with a few variations, but not many. I'm still working on the wiggle area chart, but the bar chart is primarily what I'm looking for.

Now if we can get the metrics for the bar chart area to display in the bar, that would be awesome. Although, I know that's harder to implement than a pie chart b/c of the history timestamp on the X axis getting really long.

Data:

Bucket Breakdown:

And finally, the graph:

[schoffelman (Tim Schoffelman)]

Also, here it is with all three main categories.

[tsullivan (Tim Sullivan)]

@schoffelman

Now if we can get the metrics for the bar chart area to display in the bar, that would be awesome

I'm not entirely sure how you envision that. Here's where exploring other visualization types could be handy though. Take a look at this Canvas workpad: But First, Coffee... (An Elasticon Canvas Story) | Elastic Blog

We have the different types of coffee, or "categories" with a metric attributed to each, which is total. Then for each type, there is also a sparkline, AKA date histogram.

[schoffelman (Tim Schoffelman)]

Thanks @tsullivan - i did think about using Canvas eventually, once all the visualizations are done.

And for sake of conversation, here's an example graph with the number values hovering over the bar.