How to calculate a delta value and show it in visualization

[nguyenduyhung1979 (Hung Nguyen)]

I have a data in format below:

Release Num - Server Name - CPU Average
15.4.001 - App01 - 55.56
15.4.001 - App02 - 34.12
16.1.003 - App01 - 71.04
16.1.003 - App02 - 26.82

What I want is to have a graph displaying only the differences value of CPU Average of App01 between Release Num is 15.4.001 and 16.1.003.

Could someone suggest which way to go? or what's type of visualization I should use?

Thanks,

[Marta_Bondyra]

Hi @nguyenduyhung1979, this is a good case for a derivative - you could use a vertical bar visualization, here's the example config on a date histogram:

Let me know if it answers your question.

[nguyenduyhung1979 (Hung Nguyen)]

I followed your answer. But it doesn't work for me.

I can use Vertical Bar to show CPU Average of each server between 2 releases (please see images below).

But I only want to show in chart is the delta CPU value (difference value) between 2 release only.
Base on my data, the chart should show the first column for App1 with value is 15.48 (ie: 71.04 - 55.56 = 15.48) and next column for App2 with value is -7.3 (ie: 26.82 - 34.12 = -7.3)

Any suggestion for a chart to show delta value? Thank,

[Marta_Bondyra]

Hi again,
I'm still not completely clear how your data looks like in the index pattern. How does the single document looks like? I've used devtools to mock a simple version of your index, is it something about the lines?

DELETE ti1

POST ti1/_doc
{
  "release": "15.4.001",
  "server_name": "App01",
  "cpu_avg": 55.56
}

POST ti1/_doc
{
  "release": "15.4.001",
  "server_name": "App02",
  "cpu_avg": 34.12
}

POST ti1/_doc
{
  "release": "16.1.003",
  "server_name": "App01",
  "cpu_avg": 71.04
}

POST ti1/_doc
{
  "release": "16.1.003",
  "server_name": "App02",
  "cpu_avg": 26.82
}

How, about the difference – if you want to to only display the two bars or a line (cpu average for both releases), here's the way to go – filters aggregation:

If it's crucial to display the difference of the two, it will be a bit more complicated because derivatives are only available to use on date histogram or histogram. So, firstly we need to create some numeric indicator to represent the releases as numbers. I've assigned 0 to 15.4.001 and 1 to 16.1.003 and created a scripted field release_sequence_number:

Here's the script to copy, just modify the name:

if (doc['release.keyword'].value == "15.4.001"){
    return 0;
}
if (doc['release.keyword'].value == "16.1.003" ){
    return 1;
}

Then, In my visualization I've created the following visualization that checks change value for both App1 and App2:
Config:

Preview:

Let me know if there's anything else I can help with.

[nguyenduyhung1979 (Hung Nguyen)]

It's great reply. It's crucial to display the difference of data. But can we have a scripted field which assign a number value automatically for each of new release.keyword value? Thanks

[Marta_Bondyra]

Hi @nguyenduyhung1979, unless you know the numbers ahead, I don't think so - scripted fields just have the access to current document and not others so you can't create a sequence out of values existing in the index. If it's possible, the best idea would be to create a field with number in elasticsearch before ingesting and assign a number to each release there. The other option is to keep adding 'ifs' when the new versions appear.

[nguyenduyhung1979 (Hung Nguyen)]

I think I'm good with the script (updated version below):

if (doc['release.keyword'].value == "15.4.001"){
    return 0;
} else {
    return 1;
}

and setup a filter on my dashboard where I have vertical chart show up the delta data.

[nguyenduyhung1979 (Hung Nguyen)]

One more question. Can we do some math for delta data before show it up in vertical chart?

For example:
Release Num - Server Name - CPU Average
15.4.001 - App01 - 40.00
15.4.001 - App02 - 30.00
16.1.003 - App01 - 60.00
16.1.003 - App02 - 10.00

The delta of App01 of CPU Average is: 60 - 40 = 20. But the % of increasing is 50% (comparing with 40).

I want to show the % of increasing in vertical chart, it is 50%. How can I do the math like that in scripted field?

Thanks and I'm very appreciate your help and quick reply.

[Marta_Bondyra]

if (doc['release.keyword'].value == "15.4.001"){
    return 0;
} else {
    return 1;
}

This should work unless you have some other releases versions in your index, but if not - that's the way to go.

Regarding your second question, there's unfortunately no way to do it in Vertical Bar Chart. Scripted fields cannot help here either because you can only create them based the data from the same document, but not to find corelations between separate documents (so no way to find percentage between 40 and 60 coming from different documents)

The only option at the moment is Vega visualization, but it's not trivial and requires knowing vega syntax.

[nguyenduyhung1979 (Hung Nguyen)]

You're right. I use a filter on dashboard to filter all the un-needed release, it make the chart show delta correctly now.

For Vega, it sounds like much time-investing. Let me do math for my data and next add them into the index. So show them into vertical bar chart now will be much easy.

Thanks you again so much.

[Marta_Bondyra]

You're welcome, happy to help!