# How to create tables in Kibana with operation on columns like we do in Excel? **URL:** \ **Category:** Kibana\ **Created:** [December 5, 2017, 10:33am UTC](https://discuss.elastic.co/t/how-to-create-tables-in-kibana-with-operation-on-columns-like-we-do-in-excel/110316 "2017-12-05T10:33:38Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Shalvin\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shalvin_kumar/32/24142_2.png) [@Shalvin\_Kumar](https://discuss.elastic.co/u/Shalvin_Kumar)\ **Post date:** [December 5, 2017, 10:33am UTC](https://discuss.elastic.co/t/how-to-create-tables-in-kibana-with-operation-on-columns-like-we-do-in-excel/110316/1 "2017-12-05T10:33:38Z")
Hello Community, I have completed a POC on ELK Stack in my organization and I am currently trying out few use cases that some of the Business users suggested. So I have the data of each Trade that went through us and has column like Type, Price Movement, P&L, etc. Ex: | \*\*Type | ISIN | Price Movement | P&L\*\* | | --- | --- | --- | --- | | F | R1------ | 0.2 | 10 | | F | R2------ | 0.1 | -2 | | R | R3------ | -0.1 | 3 | Now I want to generate a report in following format on Kibana from the above data that is stored in Elasticsearch : Total no. of trades : Count ( Trades where Type = F ) | Percentage ( 100% here) | Total P&L Total no. of Negative Price Movement trades : Count (Trades where Type = F and Price Movement \< 0) | Precentage = Count of previous column / Total no F trades | Sum of P& L Ex Output: Total Trades | 2 | 100% (percentage) | 8 (P&L : 10 -2) Negative Price Movement | 1 | 50% (Percentage : 1/2) | -2 (P&L) The info in brackets are only for understanding and doesn't come in Output. Please help me out on how to proceed on creating this table in Kibana. Thanks ---
**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\ **Post date:** [December 5, 2017, 5:58pm UTC](https://discuss.elastic.co/t/how-to-create-tables-in-kibana-with-operation-on-columns-like-we-do-in-excel/110316/2 "2017-12-05T17:58:59Z")
Hi @Shalvin_Kumar, assuming the transactions are represented as individual documents with fields like a `type` (as a keyword), `price_movement` (as a number) and `p_and_l` (as a number), you should be able to let Kibana calculate some of those numbers using filters and aggregations, e.g. - **total no of trades** : a filter "`type` is `F`" with the `Count` aggregation - **total p&l** : a filter "`type` is `F`" with the `Sum` aggregation on the `p_and_l` field - **total no of neg. trades** : filters "`type` is `F`" and "`price_movement` less than 0" with the `Count` aggregation - **total neg. trades p&l** : filters "`type` is `F`" and "`price_movement` less than 0" with the `Sum` aggregation on the `p_and_l` field You could visualize these values as metrics (single values) or line/bar charts (over time). The percentages require script aggregation usage, which the normal Kibana visualization UI does not support yet. ---
**Author:** ![Shalvin\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shalvin_kumar/32/24142_2.png) [@Shalvin\_Kumar](https://discuss.elastic.co/u/Shalvin_Kumar)\ **Post date:** [December 6, 2017, 7:23am UTC](https://discuss.elastic.co/t/how-to-create-tables-in-kibana-with-operation-on-columns-like-we-do-in-excel/110316/3 "2017-12-06T07:23:47Z")
Hi @weltenwort. Thanks for such prompt reply. I am now considering writing my logic in Scripted Fields and do all the operations in Metrics in Visualization. I will reach back to you if the attempt turns out to be successful or fails (and then I will work on your suggestions). Thanks again 🙂 ---
**Author:** ![Shalvin\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shalvin_kumar/32/24142_2.png) [@Shalvin\_Kumar](https://discuss.elastic.co/u/Shalvin_Kumar)\ **Post date:** [December 6, 2017, 7:38am UTC](https://discuss.elastic.co/t/how-to-create-tables-in-kibana-with-operation-on-columns-like-we-do-in-excel/110316/4 "2017-12-06T07:38:16Z")
Also, it will really helpful if you can help me with: 1. Where do I create aggregations? 2. Can you give me a sample of a filter with aggregation? 3. And how do I use those aggregations in Kibana visualizations? ---
**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\ **Post date:** [December 6, 2017, 11:04am UTC](https://discuss.elastic.co/t/how-to-create-tables-in-kibana-with-operation-on-columns-like-we-do-in-excel/110316/5 "2017-12-06T11:04:52Z")
Aggregations are used in many places in Kibana visualizations and can be mostly be selected using the UI, e.g.: ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/d/3ddbbc26fb942641ea0bbb443dd07f76af7c3226.png) Filters can be added using the filter bar, e.g.: ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/6/865c43df0d9c9d8cd17473729b3b93e8cf96c79c.png) ---
**Author:** ![Shalvin\_Kumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shalvin_kumar/32/24142_2.png) [@Shalvin\_Kumar](https://discuss.elastic.co/u/Shalvin_Kumar)\ **Post date:** [December 6, 2017, 12:26pm UTC](https://discuss.elastic.co/t/how-to-create-tables-in-kibana-with-operation-on-columns-like-we-do-in-excel/110316/6 "2017-12-06T12:26:25Z")
That's wonderful. However, can we have different aggregations using different filters in the same Visualization? My requirement is to get all the relevant data in one single place (and not in Dashboard preferably)? Also, as I am currently trying Scripted Fields I am almost done except the percentage part where I need to calculate `sum of P&L of cat-1 / sum of Total P&L` . It will be great if you can give me some pointers on how to resolve this. Really appreciate the help. ---
**Author:** ![weltenwort](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/weltenwort/32/53885_2.png) [@weltenwort](https://discuss.elastic.co/u/weltenwort)\ **Post date:** [December 7, 2017, 5:36pm UTC](https://discuss.elastic.co/t/how-to-create-tables-in-kibana-with-operation-on-columns-like-we-do-in-excel/110316/7 "2017-12-07T17:36:07Z")
Combining all this in a single visualization could be difficult. That's exactly what the dashboards are meant to do. What keeps you from using those? Calculating ratios across documents can not be done using scripted fields. The Time Series Visual Builder has a "Filter Ratio" aggregation, that can do that though: ![image](https://us1.discourse-cdn.com/elastic/original/3X/3/c/3cbc602ecb0de23cef8dcee52ebc326c6eaf1146.png) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [January 4, 2018, 5:36pm UTC](https://discuss.elastic.co/t/how-to-create-tables-in-kibana-with-operation-on-columns-like-we-do-in-excel/110316/8 "2018-01-04T17:36:18Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.