Kibana - aggregation in graphs (average number of things per bucket)

Elastic Stack Kibana
1

Hi,
I have a situation and I am stuck, I think what I want to do is not currently achievable but I hope I'm wrong.
Let's assume I have the following data about people going to the movies:

  • Name of person
  • Which movies they watched
  • Timestamp of when the movie played
  • Whatever other information (sitting information, payment etc.)

Now I want to know what is the average people that went to see movies at a certain date
So I create a graph, and the horizontal axis will be a timestamp. but now my only option is -
Either just show the count of people, regardless of movies, or I can breakdown by movies, but then I will see each movie individually and will not have a graph of the average.
If I use a table - I can see the individual movies, and also a bottom line saying what the average number people was, but I can't find a way to translate it into graphs. I hoped that I can use functions for that, but there is no aggregation option in functions

Is there a way to do that? am I missing anything?

2

Hi @RonGros , is your data denormalized? If it is denormalized, I.E each row represents one time one person watched one movie, you can easily bucket by date in Lens, then unique count of name would be the number of people who saw a movie in that time period. If you're calculating an average, you'd need to calculate it over multiple buckets, whereas a graph would show a value per bucket, so I'm not exactly sure what the average you're looking for is.

3

Hi @devon.thomson !
yes, my data is denormalized, exactly like you described it.
what I want to know is a single number (per day) telling me - what is the average number of people to watch any movie, without going into specific movies. it is easy to see the average number of people going to movie X or Y, but what I want is - what is the average number of people in movies as a whole, and I want a single number.
So for example, if I had 4 movies, and on August 1st I have 2 record for movie 1, 10 records for movie 2, and 6 records for movies 3+4, I want something to tell me that the average for August 1st is 6

4

Great, thanks for the context! That makes sense.

It seems like you can get this average by dividing the total number of unique people on the day by the total number of movies shown that day. You should check out Lens formulas!

5

@devon.thomson you're right! that's actually quite simple, not sure why I haven't thought of it, thanks! (BTW I Am well aware of the lens formulas)
Now here is a bigger challenge - how do I find the maximum number of people that attended a movie at the same day...?

6

@devon.thomson any idea?

7

Hey @RonGros, If I was going about this, I would change the data to have a parsed dayOfScreening field made from rounding the timestamp to the day. You could do that at ingest time, which is how I would do it, or you could create a scripted field. Here is an example of something similar being done.

In my opinion, with elasticsearch it is always better to think your data problems through from the ingestion side, even if it results in repeated information.

8

Hi Devon, I already have such a field, but how will that help me?

9

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.