Vega lite Top 10 order host descending based on the sum of field score

Community Help
#1

I have this example data

"values": [
  {"host": "host-one", "score": 70, date: 1593674604},
  {"host": "host-two", "score": 30, date: 1593674608},
  {"host": "host-three", "score": 12, date: 1593674615},
  {"host": "host-two", "score": 95, date: 1593674624},
  {"host": "host-three", "score": 1, date: 1593674633}    
]

In a heatmap I have to show top 10 host, based on the sum of the score per host, in the y axis, I have tried in many ways without success, this is what I have tried the last time:

 }
  transform: [
    {calculate: "datum.key.date", as: "date"}
    {calculate: "datum.key.host", as: "host"}
    {calculate: "datum.key.score", as: "score"}
    {
      window: [
        {op: "sum", field: "score", as: "score_order"}
      ]
    }
    groupby: ["host"]
  ]
  mark: {type: "rect", cornerRadius: 3}
  encoding: {
    x: {
      bin: {maxbins: 80}
      field: date
      type: temporal
    }
    y: {
      field: host
      type: ordinal
      sort: {field: "score_order", order: "descending"}
    }

Any clues on what Im doing wrong?

#2

Syntax and logic errors. See this repro/fix in Vega-lite editor.

1 Like
#3

Hi Mario, thanks for you answer, and sorry for the sintax, Im working with Vega-lite in kibana, and there you can get rid of the comas and quotes, an the data source is Elasticsearch. ill keep that in mind the next time I post code.

In the repo/fix that you linked the host are not ordered by score, the first host on the top is host-one, and should be host-two because it has a sum of 125, then host-one (70) and host-three(13).

#4

In that scenario, I think your problem is with the binning. Documentation states:

Usually, you should set the type of binned encodings to be quantitative. Vega-Lite automatically creates axes and legends that best represent binned data. However, if you want to sort the bins or skip empty bins, you can set the type to ordinal.

Take a look at this chart that adds a ‘sum’ op to the y encoding. The x axis dates are still binned out individually as the ‘host-two’ are properly sorted first.

If the specific dates for ‘host-two’ end up in the same bin, I think this will give you what you want; as this chart demonstrates.

1 Like