I'm struggling to create a heatmap that will show Success Rate in Vega-lite. My data is similar to the below.

[
   {
      "_id":"1",
      "@timestamp":"2022-02-20 11:15:16",
      "transaction_datetime":"2022-02-20 11:15:16",
      "type_a_partner":Partner1,
      "type_b_partner":Partner2,
      "status":"Success"
   },
   {
      "_id":"2",
      "@timestamp":"2022-02-20 11:16:16",
      "transaction_datetime":"2022-02-20 11:16:16",
      "type_a_partner":Partner1,
      "type_b_partner":Partner2,
      "status":"Failed"
       }
]

Success Rate calculation would be: ((count of records where status="Success")/(count of records))*100

I have tried to create multiple transforms but seems it does not work that way. Currently, I'm trying with the below code.

{
  $schema: https: //vega.github.io/schema/vega-lite/v2.json
  "width": "container",
  "height": 400,
  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" },
  },  
  transform: [
        { 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"},
        { "filter":  "datum._source['status']=='Success'"},
        {
      "aggregate": [
        {"op": "count", "as": "SuccessTxns"}
      ],
      "groupby": ["type_a_partner","type_b_partner"]
        }
    ],  
  mark:"bar",
  "width": {"step": 17},
  encoding: {
    x: { field: "type_a_partner"},
    y: { field: "type_b_partner"}, 
    "color": {"field": "SuccessTxns", type:"quantitative"},
    tooltip : [
    {field : "type_b_partner",type:"nominal"}
    {field : "type_a_partner",type:"nominal"}
    {field : "SuccessTxns", type:"quantitative"}
    ]
    }
  "layer": [{
    "mark": "bar"
  }, {
    "mark": {
      "type": "text",
      "align": "left",
      "baseline": "middle",
      "dx": -9
    },
    "encoding": {
      "text": {field: "SuccessTxns", type: "quantitative"}
    }
  }]

  
  
  
}

I'm able to extract Successful transaction count (SuccessTxns) but unable to create a field for "Total Transaction" using transform.

The visual would be like this. Sample visualization

Note: Sample heatmap Visual is attached to visualize the requirement only. Each square will show the Success rates

Please excuse me if I made any logical mistake to frame the objective or was unable to set the context properly. If you have any queries regarding the problem statement, please let me know.

Regards,

CodePudding user response：

Resolved. 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"}
    ]