Heatmap will show success rate using vega-lite or vega

[Souvik_Das]

Hi Everyone,

I need to create a heatmap that will show the success rate for the two Types of Partners. Now let me brief you about my requirement.

We have transaction-level data for two types of partners, ex: Type A, Type B, now each Type has a number of partners and we need to show the success rate in the heatmap where, in the x-axis, we have Type A Partner's names and in the y-axis, we have Type B Partner's names and color will be indicating the rate. Sample heatmap Visual is attached to visualize the requirement only.
Transactions can be successful or failed, and there is a field named "status" which has two values, i.e, "success", "fail".
I'm unable to create the heatmap using the default heatmap visual provided by kibana because we cannot use custom calculation in the default visuals. That is the reason I'm exploring vega and vega-lite to find out the solution.

My success calculation would be (Count of transactions where status=success)/(count of all transactions) * 100

I'm unable to create it using vega/vega-lite transform.

Hope I'm able to brief you properly. If you need any further information regarding the requirement, please let me know.

Please help me to build the visual.

Thanks in advance!

[aaron-nimocks]

If you can provide your Vega spec that includes the data that would help someone be able to assist with this.

Without seeing anything it looks like you need to do 2 transforms in Vega. Most likely you need an aggregate then a formula to do the calculation.

[Souvik_Das]

Sure @aaron-nimocks , let me share the vega spec that I have tried with.

{
  $schema: https: //vega.github.io/schema/vega-lite/v2.json
  "width": "container",
  "height": 400,
  data: {
    url: {
      /* %context%: true
      %timefield%: transaction_datetime */
      index: index1
      body: {
        /* size: 3000, */
		"query":{
			"range": {"transaction_datetime": {"%timefilter%": true}}
		},
        _source: [
                "@timestamp",
                "transaction_datetime"
                "type_b_partner"
                "type_a_partner"
                "status"
                "_id"
            ]
        }
    },
    format: { property: "hits.hits" },
  },  
  transform: [
		{
		"window": [{
		"op": "count",
		"field": "_id",
		"as": "TotalTxns"
				}],
		},
        { calculate: "datum._source['status']" as: "status"},
        { calculate: "datum._source['type_b_partner']" as: "type_b_partner"},
		{ calculate: "datum._source['type_a_partner']" as: "type_a_partner"},
        { calculate: "datum._source['_id']" as: "id"},
		{ calculate: "datum.TotalTxns * 100" as: "rate"}
		
        
    ],
  mark:"bar",
  "width": {"step": 17},
  encoding: {
    x: { field: "type_a_partner"},
    y: { field: "type_b_partner"}, 
	"color": {"field": "rate", type:"quantitative"},
    tooltip : [
    {field : "type_b_partner",type:"nominal"}
	{field : "type_a_partner",type:"nominal"}
    {field : "rate", type:"quantitative"}
    ]
	}
  "layer": [{
    "mark": "bar"
  }, {
    "mark": {
      "type": "text",
      "align": "left",
      "baseline": "middle",
      "dx": -9
    },
    "encoding": {
      "text": {field: "rate", type: "quantitative"}
    }
  }]

  
  
  
}

But please note, it is not the one that will give me the desired output. Here using transform, I have tried to get total number of transaction and tried to use "TotalTxns" field in the calculate section and later the calculated field i.e., "rate" in the visual part. My objective is to get "Total Number of Transactions" and "Successful Transactions". By using these two, I need to calculate "Success Rate". Hope this clarifies my objective.
Note: the "rate" calculated field is just a placeholder, not the actual calculation.

@aaron-nimocks, Please resolve my below queries.

1. How to create two calculated fields using two transforms in vega-lite? It is better if you can share some examples.
2. If I need "count of rows" from an index. How to define that in vega-lite?

Please excuse me if I made any logical mistake to frame the objective or was unable to clarify properly.

[aaron-nimocks]

If you can provide your Vega spec following the instructions in the link it will help. When you do it that way it will capture the data as well which is needed to see what's going on.

[Souvik_Das]

Hi @aaron-nimocks, Thanks for the reference. Could you please share some examples of 2 transforms in a single vega-lite spec? I'm unable to find such examples. While I'm exploring vega-lite, I found that I might need 2 transforms as you mentioned in your earlier post but I'm unable to do so.

[aaron-nimocks]

If you are using Vega-Lite then you would use aggregate and calculate most likely. There are examples on the pages but most likely won't be exactly what you need depending on your data.

It might be worth your time to view all the examples and check the spec to help learn what's going on.

[Souvik_Das]

Resolved. @aaron-nimocks Thanks for your suggestion. Below is the solution.

transform: [
        { calculate: "datum._source['status']" as: "status"},
        { calculate: "datum._source['type_a_partner']" as: "type_a_partner"},
		{ calculate: "datum._source['type_b_partner']" as: "type_b_partner"},
        { calculate: "datum._source['_id']" as: "id"},
		{"calculate": "datum.status === 'Success' ? 1 : 0", "as": "SC"},
		{
      "aggregate": [
        {"op": "count", "as": "TotalCount"},
		{"op": "sum","field": "SC","as": "SuccessCount"}
      ],
      "groupby": ["type_b_partner","type_a_partner"]
		},
		{"calculate": "datum.SuccessCount / datum.TotalCount * 100","as": "Rate"}
	]

@aaron-nimocks , Need another help,

  data: {
    url: {
      %context%: true
      %timefield%: transaction_datetime
	    %timefilter%: "min"
	    %timefilter%: "max"
      index: index1
      body: {
         size: 10000,
        _source: [
                "@timestamp",
                "transaction_datetime"
                "type_b_partner"
                "type_a_partner"
                "status"
                "_id"
            ]
        }
    },
    format: { property: "hits.hits" },
  }

This is the input section. I have given size: 10000, but I cannot increase further as it was throwing "EsError". Because of this reason, all the documents were not considered by the calculation which in terms caused wrong success rates.

How to increase the size to more than 10000? Or how to take all the records for the timeframe I have selected from the TimeFilter of Kibana?

[aaron-nimocks]

@Souvik_Das

	    %timefilter%: "min"
	    %timefilter%: "max"

Your query inside the body should include these parameters like the example below. It will replace the min/max value of the timepicker into those variables but must be using within the query. Usually a date range.

        body: {
          aggs: {
            time_buckets: {
              date_histogram: {
                field: order_date
                fixed_interval: "3h"
                extended_bounds: {
                  min: {%timefilter%: "min"}
                  max: {%timefilter%: "max"}
                }
                min_doc_count: 0
              }
            }
          }
          size: 0
        }

What I would do first is create a query in Dev Tools that will use the time fields as a range to narrow down your results. Then transfer that DSL query over to Vega.

Another option is change the index.max_result_window default value in the index setting but it should be noted that changing this could cause severe performance issues. Tread lightly.