Percentage Calculation in kibana

[Ramadevi]

Hi Team,

Our goal is to build table in the below format

Marketing	Operating	Count	Total	AF	FL	Total(%)	AF(%)	FL(%)
UA	UA	9415	24	17	7	0.25	0.18	0.07
UA	LH	47	6	6	0	12.77	12.77	0

We are able to create below format using data-table

Marketing	Operating	Count	Total	AF	FL
UA	UA	9415	24	17	7
UA	LH	47	6	6	0

Percentages should be calculated w.r.t Count

We tried with scripted fields for calculating percentage, but couldn't calculate percentage and we got to know that scripted fields are calculated for each document due to which we couldn't achieve it.

Please refer below for more details - Division in Scripted field

Our current version of kibana is 7.4.2. Could you please suggest best possible way to implement this.

Also do let us know if we can achieve this by taking latest kibana version.

Thanks & Regards,
Ramadevi

[flash1293 (Joe Reuter)]

The TSVB table visualization allows you to calculate percentages of other columns, but you can't use multiple buckets (like Marketing and Operating in your example).

I think right now this is only possible in Canvas as you can use SQL there to query your table.

[Ramadevi]

Hi Joe,

Our goal is to implement table and send mail with table.

When we checked Canvas, we could only see download format as pdf but not datatable.

Could you please suggest.

Also we are in 7.4.2 version, could you please suggest if we have an option for sending automated mail through kibana in any specific version of kibana.

[flash1293 (Joe Reuter)]

When we checked Canvas, we could only see download format as pdf but not datatable.

Do you mean you can't export it as a CSV file? This is indeed not possible at the moment, but you can export the whole canvas sheet as PDF. This PDF will include the same information in a more readable form (also possible to automatically schedule this to send it via mail):

[Ramadevi]

Could you please suggest procedure for this - also possible to automatically schedule this to send it via mail

[flash1293 (Joe Reuter)]

Check out https://www.elastic.co/guide/en/kibana/current/automating-report-generation.html, it describes your use case

[Ramadevi]

Query 1: We dont have Reporting/Watcher in version we are using.Actually we just have kibana url(7.4.2).How can we get reporting/watcher.Should we get latest version.

2. We tried to download json format for table, but after download we could notice we are getting only sql query but not tabular data

{
  "css": ".canvasPage {\n\n}",
  "id": "workpad-80dc2782-0eba-476b-9056-f6c1db412469",
  "name": "Untitled Workpad",
  "width": 1080,
  "height": 720,
  "page": 0,
  "pages": [
    {
      "id": "page-76b9e017-7a80-45c6-a92f-ba8d7823d76d",
      "style": {
        "background": "#FFF"
      },
      "transition": {
      },
      "elements": [
        {
          "id": "element-04f9513a-0e94-4ae9-b7c7-4bd4c2367e46",
          "position": {
            "left": 20,
            "top": 20,
            "width": 500,
            "height": 300,
            "angle": 0
          },
          "expression": "filters\n| essql query=\"SELECT * FROM \\\"rmp_*\\\"\"\n| table\n| render"
        }
      ],
      "groups": [
      ]
    }
  ],
  "colors": [
    "#37988d",
    "#c19628",
    "#b83c6f",
    "#3f9939",
    "#1785b0",
    "#ca5f35",
    "#45bdb0",
    "#f2bc33",
    "#e74b8b",
    "#4fbf48",
    "#1ea6dc",
    "#fd7643",
    "#72cec3",
    "#f5cc5d",
    "#ec77a8",
    "#7acf74",
    "#4cbce4",
    "#fd986f",
    "#a1ded7",
    "#f8dd91",
    "#f2a4c5",
    "#a6dfa2",
    "#86d2ed",
    "#fdba9f",
    "#000000",
    "#444444",
    "#777777",
    "#BBBBBB",
    "#FFFFFF",
    "rgba(255,255,255,0)"
  ],
  "isWriteable": true,
  "assets": {
  },
  "@timestamp": "2020-08-18T15:33:29.898Z",
  "@created": "2020-08-18T15:32:54.943Z"
}

3.Is there a possibility to invoke kibana url to download tabular data from canvas

[flash1293 (Joe Reuter)]

No, there is no way to do this. Reporting and watcher are under the gold license, so you need a subscription for this: https://www.elastic.co/subscriptions