How to create tables in Kibana with operation on columns like we do in Excel?

Elastic Stack Kibana
1

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

2

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.

3

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 :slight_smile:

4

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?
5

Aggregations are used in many places in Kibana visualizations and can be mostly be selected using the UI, e.g.:

image
image.png1040×753 42.7 KB

Filters can be added using the filter bar, e.g.:

image
image.png1048×758 48.2 KB

6

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.

7

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
image.png1323×262 13 KB

8

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