# Make visualizations (metric and table/topn) with weighted average **URL:** \ **Category:** Kibana\ **Created:** [November 13, 2020, 9:53am UTC](https://discuss.elastic.co/t/make-visualizations-metric-and-table-topn-with-weighted-average/255316 "2020-11-13T09:53:24Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Fabio-sama](https://avatars.discourse-cdn.com/v4/letter/f/b9e5f3/32.png) [@Fabio-sama](https://discuss.elastic.co/u/Fabio-sama)\ **Post date:** [November 13, 2020, 9:53am UTC](https://discuss.elastic.co/t/make-visualizations-metric-and-table-topn-with-weighted-average/255316/1 "2020-11-13T09:53:24Z")
Hi there, I'm struggling trying to visualize a Metric visualization with a given weighted average over a certain timespan. Let's consider the following scenario: index: sample\_shops\_data fields of interest: day (date), shop\_id (text), shortmessage (text) sample log: 2020-11-11 | X | foo possible values of shop\_id: (X, Y, Z). possible values of shortmessage: (foo, bar, whatever) Let's consider the following situation concerning some collected logs, all of them with `shortmessage: foo` ``` 2020-11-06: 3 affected shop_ids, 47 total events (X=14, Y=14, Z=19) 2020-11-07: 0 affected shop_ids, 0 total events (X=0, Y=0, Z=0) 2020-11-08: 2 affected shop_ids, 55 total events (X=23, Y=32, Z=0) 2020-11-09: 2 affected shop_ids, 29 total events (X=15, Y=0, Z=14) 2020-11-10: 3 affected shop_ids, 38 total events (X=11, Y=15, Z=12) 2020-11-11: 3 affected shop_ids, 30 total events (X=12, Y=6, Z=12) 2020-11-12: 3 affected shop_ids, 50 total events (X=21, Y=11, Z=18) ``` ![index_discover](https://us1.discourse-cdn.com/elastic/original/3X/8/f/8f751cf47acb85539a7b272dd4a92c4814b19444.png) Average number of "foo" events by shop\_id per day: ((47/3) + 0 + (55/2) + (29/2) + (38/3) + (30/3) + (50/3)) / 7 = **13.85** Obviously, other than weight the average dividing each daily total by the number of the shop\_ids affected that day, it also has to consider those days where no shop\_id has been affected (for example those days where no "foo" shortmessage has been ingested). Furthermore, I'd like to have another visualization (Table or TopN for example) listing all those shop\_ids whose average over the same timespan is greater than that 13.85. In this specific case: ``` X --> (14+0+23+15+11+12+21)/7 = 13.71 Y --> (14+0+32+0+15+6+11)/7 = 11.14 Z --> (19+0+0+14+12+12+18)/7 = 10.71 ``` none of them would be visualized since they're all below 13.85 threshold. How can I build such visualizations? I guess using something like Bucket Script or Math in the TSVB but I cannot find the right settings. Possibly I might have to mix something like the Cardinality with the Overall Average or Moving Average and compute the weighted part with a Bucket Script, but I cannot find the right syntax nor the right Interval settings to set in the Panel Options to make it work. Any ideas? Thanks! ---
**Author:** ![Fabio-sama](https://avatars.discourse-cdn.com/v4/letter/f/b9e5f3/32.png) [@Fabio-sama](https://discuss.elastic.co/u/Fabio-sama)\ **Post date:** [November 13, 2020, 1:48pm UTC](https://discuss.elastic.co/t/make-visualizations-metric-and-table-topn-with-weighted-average/255316/2 "2020-11-13T13:48:52Z")
Ok with the help of some colleagues of mine I think we found a partial solution to this issue. Let's consider the following scheme (something changed in terms of docs count but the general concept is always the same) ``` 2020-11-06: 3 affected shop_ids, 141 total events (X=50, Y=42, Z=49) 2020-11-07: 0 affected shop_ids, 0 total events (X=0, Y=0, Z=0) 2020-11-08: 2 affected shop_ids, 55 total events (X=23, Y=32, Z=0) 2020-11-09: 2 affected shop_ids, 29 total events (X=15, Y=0, Z=14) 2020-11-10: 3 affected shop_ids, 38 total events (X=11, Y=15, Z=12) 2020-11-11: 3 affected shop_ids, 77 total events (X=37, Y=17, Z=23) 2020-11-12: 3 affected shop_ids, 50 total events (X=21, Y=11, Z=18) ``` ((141/3) + 0 + (55/2) + (29/2) + (38/3) + (77/3) + (50/3)) / 7 = **20.57** ``` X --> (50+0+23+15+11+37+21)/7 = 22.42 Y --> (42+0+32+0+15+17+11)/7 = 16.71 Z --> (49+0+0+14+12+23+18)/7 = 16.57 ``` I had to add something in the ingestion phase to extrapolate only the date (YYYY-MM-dd) from the timestamp in order to use the group by in the TSVB. This is what we managed to achieve ![average_results](https://us1.discourse-cdn.com/elastic/original/3X/a/c/ac0b04fe498e7b26eb425f339c2b6f9ffdc36130.png) So what we did for the Metric in the TSVB was the following: ![metric_data_settings](https://us1.discourse-cdn.com/elastic/original/3X/b/9/b9d8d6a8b3dedb2e97bb9a846048cf7434de37cc.png) ![metric_option_settings](https://us1.discourse-cdn.com/elastic/original/3X/d/3/d36c57b5a6e169d368178a7171f7d1954866b6ec.png) As you can see in the `Data Settings` we had to group by day to achieve what we wanted. At first we tried with the @timestamp but it's not feasible when the events differ by milliseconds (we'd need an unthinkable number in the `Top` setting of the `Group by` part). As for the Table, we left the same settings and simply switch to TSVB Table view, grouping by the shop\_id.keyword field. Unfortunately, we didn't manage to find a way to filter the table rows leaving only those rows greater than what was computed by the Metric by at this point I don't think whether it is feasible or not. If you find a way, please let me know. Thanks! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 11, 2020, 1:49pm UTC](https://discuss.elastic.co/t/make-visualizations-metric-and-table-topn-with-weighted-average/255316/3 "2020-12-11T13:49:06Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.