# Complex calculations and table view format

Hello all,

i would like to know if this kind of tables can be done with kibana and add it to a dasboard. What is the best approach to achieve this?

Index structure

``````{
"gain_loss" => " 1,011.53",
"@timestamp" => 2020-01-06T23:00:00.000Z
}
``````

Goal: make calculations over gain_loss field and create a view in table/s format like this:

``````Daily basis
Sum positives: 1000
Sum negatives: -3000
Sum positives / Sum negatives ratio: 0,33
Max positive: 1300
Max negative: -2000
Avg: 200

Monthly basis
Sum positives: 3000
Sum negatives: -6000
Sum positives / Sum negatives ratio: 0,5
Max positive: 2000
Max negative: -3000
Avg: 700
``````

Best regards

Hello all,

Any ideas?. Maybe my question it is not clear.

Best regards

Hey @nino!

Should be doable! Some tips:

• Looks like you might be indexing a number as a string — this will make math difficult to impossible.
• Create two visualizations when trying to compare two time periods then you can compare them side-by-side in a dashboard
• To find “positives” and “negatives”, use `Filters` or `Filter Ratio` . For example, `Max(gain_loss)` with a filter `gain_loss>0` would be max positives.
• You can setup time filters in the table itself or in the dashboard you can change the time range for individual panels (independent of the global time range)

Edit: Previously this comment said it wasn't possible but on second thought you should be able to with the above tips!

Thank you so much @myasonik,

yes i used filter ratio to achieve this but what is really a pain is to create a table with all the calculations to add it in a dashboard. What about the view? I've tried TSVB and markdown but you can only add two calculations.

gain_loss is a numeric field

View 1 (statistics over the field gain_loss in a daily basis)

``````Sum positives: 1000
Sum negatives: -3000
Sum positives / Sum negatives ratio: 0,33
Max positive: 1300
Max negative: -2000
Avg: 200
``````

View 2 (stats over the field gail_loss in a monthly basis)

``````Sum positives: 1000
Sum negatives: -3000
Sum positives / Sum negatives ratio: 0,33
Max positive: 1300
Max negative: -2000
Avg: 200
``````

No way to add more than too calculations with TSVB.

Any more ideas to work around this?

Hmmm... I believe your ratio cell won't be possible inside of Kibana at the moment as there's no way to compute one cell off of another cell.

You might want to look into some pivot table plugins for Kibana (of which there are multiple) which might make creating something like this easier.

Thank you @myasonik,

maybe i have to abandon kibana for visualization. I think kibana is very limited when you want to make a dashboard a bit more sophisticated. I need to make computations over the series like R or Python way, drawdowns, ratios, monthlies, dailies.

Any ideas to face this with kibana or alternative to replace kibana?

Dear all,

do you know an alternative to kibana to replace the visualization part of an ELK?. I need to achieve visualizations like:

Table of metrics (without timestamp)

``````+-----------------+-------+
|     Metric      | Value |
+-----------------+-------+
| Net performance |  1000 |
| Drawdown        |  -200 |
| Positives       |    20 |
| Negatives       |    10 |
| Ratio           |  0.50 |
+-----------------+-------+
``````

Maybe, creating this table out of kibana? Creating and index with this structure and then send it to elastic?

``````{
"Net performance" => "1000",
"Drawdown" => "200",
"Positives" => "20",
"Negatives" => "10",
"Ratio" => "0.50",
}
``````

Maybe not efficient to create this out of kibana because i have to read the whole data when a new data point arrives, but i don't know how to make computations on the fly with kibana.

What do you think?

Metrics and Calculates fields

How do you calculate metrics or fields based on document fields?, for example the drawdown. Did you make the computations befofe sending the index to elastic?. For example, make the calculations in a logstash pipeline.

I need to know the way to go with this kind of issues.

Best regards,
Vince.

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