Problem in sorting dual axis graph

Elastic Stack Kibana
1

Hi,

Plotted bar and line graph together, tried to sort bar graph and expected line graph to sort accordingly. where as line graph was sorted in weird manner.

Output :

image

Expected Output :

image
image858×174 14.6 KB

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "name": "trellis_barley",
  "data": {"url": "data/barley.json"},
  "transform": [
     {
        "joinaggregate": [{"op": "median", "field": "yield","as": "k"}],
    "groupby": ["variety"]
      }
  ],
   "encoding":{
    "x": {"field": "variety", "type": "ordinal","sort":{"field": "k", "order": "descending"}
}
   },
  "layer":[{
  "mark": "bar",
  "encoding": {
    "y": {
      "aggregate": "median",
      "field": "k",
      "type": "quantitative",
      "scale": {"zero": false}
    }

  }
  },
  {
  "mark": "line",
  "encoding": {
    "y": {
      "aggregate": "sum",
      "field": "yield",
      "type": "quantitative",
      "scale": {"zero": false},
      "sort":{"field": "k", "order": "descending"}
    }
  }
  }],   "resolve": {
            "scale": {"y": "independent"}
        }
  
}
2

Hey @Pranusha119

I think I was able to produce what you're looking for by creating an aggregate transform for the sum, and then using that to plot the line chart. Is this what you're looking for?

{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "name": "trellis_barley",
  "data": {
    "values": [
      { "yield": 27, "variety": "Manchuria", "year": 1931, "site": "University Farm" },
      { "yield": 48.86667, "variety": "Manchuria", "year": 1931, "site": "Waseca" },
      { "yield": 27.43334, "variety": "Manchuria", "year": 1931, "site": "Morris" },
      { "yield": 39.93333, "variety": "Manchuria", "year": 1931, "site": "Crookston" },
      { "yield": 32.96667, "variety": "Manchuria", "year": 1931, "site": "Grand Rapids" },
      { "yield": 28.96667, "variety": "Manchuria", "year": 1931, "site": "Duluth" },
      { "yield": 43.06666, "variety": "Glabron", "year": 1931, "site": "University Farm" },
      { "yield": 55.2, "variety": "Glabron", "year": 1931, "site": "Waseca" },
      { "yield": 28.76667, "variety": "Glabron", "year": 1931, "site": "Morris" },
      { "yield": 38.13333, "variety": "Glabron", "year": 1931, "site": "Crookston" },
      { "yield": 29.13333, "variety": "Glabron", "year": 1931, "site": "Grand Rapids" },
      { "yield": 29.66667, "variety": "Glabron", "year": 1931, "site": "Duluth" },
      { "yield": 35.13333, "variety": "Svansota", "year": 1931, "site": "University Farm" },
      { "yield": 47.33333, "variety": "Svansota", "year": 1931, "site": "Waseca" },
      { "yield": 25.76667, "variety": "Svansota", "year": 1931, "site": "Morris" },
      { "yield": 40.46667, "variety": "Svansota", "year": 1931, "site": "Crookston" },
      { "yield": 29.66667, "variety": "Svansota", "year": 1931, "site": "Grand Rapids" },
      { "yield": 25.7, "variety": "Svansota", "year": 1931, "site": "Duluth" },
      { "yield": 39.9, "variety": "Velvet", "year": 1931, "site": "University Farm" },
      { "yield": 50.23333, "variety": "Velvet", "year": 1931, "site": "Waseca" },
      { "yield": 26.13333, "variety": "Velvet", "year": 1931, "site": "Morris" },
      { "yield": 41.33333, "variety": "Velvet", "year": 1931, "site": "Crookston" },
      { "yield": 23.03333, "variety": "Velvet", "year": 1931, "site": "Grand Rapids" },
      { "yield": 26.3, "variety": "Velvet", "year": 1931, "site": "Duluth" },
      { "yield": 36.56666, "variety": "Trebi", "year": 1931, "site": "University Farm" },
      { "yield": 63.8333, "variety": "Trebi", "year": 1931, "site": "Waseca" },
      { "yield": 43.76667, "variety": "Trebi", "year": 1931, "site": "Morris" },
      { "yield": 46.93333, "variety": "Trebi", "year": 1931, "site": "Crookston" },
      { "yield": 29.76667, "variety": "Trebi", "year": 1931, "site": "Grand Rapids" },
      { "yield": 33.93333, "variety": "Trebi", "year": 1931, "site": "Duluth" },
      { "yield": 43.26667, "variety": "No. 457", "year": 1931, "site": "University Farm" },
      { "yield": 58.1, "variety": "No. 457", "year": 1931, "site": "Waseca" },
      { "yield": 28.7, "variety": "No. 457", "year": 1931, "site": "Morris" },
      { "yield": 45.66667, "variety": "No. 457", "year": 1931, "site": "Crookston" },
      { "yield": 32.16667, "variety": "No. 457", "year": 1931, "site": "Grand Rapids" },
      { "yield": 33.6, "variety": "No. 457", "year": 1931, "site": "Duluth" },
      { "yield": 36.6, "variety": "No. 462", "year": 1931, "site": "University Farm" },
      { "yield": 65.7667, "variety": "No. 462", "year": 1931, "site": "Waseca" },
      { "yield": 30.36667, "variety": "No. 462", "year": 1931, "site": "Morris" },
      { "yield": 48.56666, "variety": "No. 462", "year": 1931, "site": "Crookston" },
      { "yield": 24.93334, "variety": "No. 462", "year": 1931, "site": "Grand Rapids" },
      { "yield": 28.1, "variety": "No. 462", "year": 1931, "site": "Duluth" },
      { "yield": 32.76667, "variety": "Peatland", "year": 1931, "site": "University Farm" },
      { "yield": 48.56666, "variety": "Peatland", "year": 1931, "site": "Waseca" },
      { "yield": 29.86667, "variety": "Peatland", "year": 1931, "site": "Morris" },
      { "yield": 41.6, "variety": "Peatland", "year": 1931, "site": "Crookston" },
      { "yield": 34.7, "variety": "Peatland", "year": 1931, "site": "Grand Rapids" },
      { "yield": 32, "variety": "Peatland", "year": 1931, "site": "Duluth" },
      { "yield": 24.66667, "variety": "No. 475", "year": 1931, "site": "University Farm" },
      { "yield": 46.76667, "variety": "No. 475", "year": 1931, "site": "Waseca" },
      { "yield": 22.6, "variety": "No. 475", "year": 1931, "site": "Morris" },
      { "yield": 44.1, "variety": "No. 475", "year": 1931, "site": "Crookston" },
      { "yield": 19.7, "variety": "No. 475", "year": 1931, "site": "Grand Rapids" },
      { "yield": 33.06666, "variety": "No. 475", "year": 1931, "site": "Duluth" },
      { "yield": 39.3, "variety": "Wisconsin No. 38", "year": 1931, "site": "University Farm" },
      { "yield": 58.8, "variety": "Wisconsin No. 38", "year": 1931, "site": "Waseca" },
      { "yield": 29.46667, "variety": "Wisconsin No. 38", "year": 1931, "site": "Morris" },
      { "yield": 49.86667, "variety": "Wisconsin No. 38", "year": 1931, "site": "Crookston" },
      { "yield": 34.46667, "variety": "Wisconsin No. 38", "year": 1931, "site": "Grand Rapids" },
      { "yield": 31.6, "variety": "Wisconsin No. 38", "year": 1931, "site": "Duluth" },
      { "yield": 26.9, "variety": "Manchuria", "year": 1932, "site": "University Farm" },
      { "yield": 33.46667, "variety": "Manchuria", "year": 1932, "site": "Waseca" },
      { "yield": 34.36666, "variety": "Manchuria", "year": 1932, "site": "Morris" },
      { "yield": 32.96667, "variety": "Manchuria", "year": 1932, "site": "Crookston" },
      { "yield": 22.13333, "variety": "Manchuria", "year": 1932, "site": "Grand Rapids" },
      { "yield": 22.56667, "variety": "Manchuria", "year": 1932, "site": "Duluth" },
      { "yield": 36.8, "variety": "Glabron", "year": 1932, "site": "University Farm" },
      { "yield": 37.73333, "variety": "Glabron", "year": 1932, "site": "Waseca" },
      { "yield": 35.13333, "variety": "Glabron", "year": 1932, "site": "Morris" },
      { "yield": 26.16667, "variety": "Glabron", "year": 1932, "site": "Crookston" },
      { "yield": 14.43333, "variety": "Glabron", "year": 1932, "site": "Grand Rapids" },
      { "yield": 25.86667, "variety": "Glabron", "year": 1932, "site": "Duluth" },
      { "yield": 27.43334, "variety": "Svansota", "year": 1932, "site": "University Farm" },
      { "yield": 38.5, "variety": "Svansota", "year": 1932, "site": "Waseca" },
      { "yield": 35.03333, "variety": "Svansota", "year": 1932, "site": "Morris" },
      { "yield": 20.63333, "variety": "Svansota", "year": 1932, "site": "Crookston" },
      { "yield": 16.63333, "variety": "Svansota", "year": 1932, "site": "Grand Rapids" },
      { "yield": 22.23333, "variety": "Svansota", "year": 1932, "site": "Duluth" },
      { "yield": 26.8, "variety": "Velvet", "year": 1932, "site": "University Farm" },
      { "yield": 37.4, "variety": "Velvet", "year": 1932, "site": "Waseca" },
      { "yield": 38.83333, "variety": "Velvet", "year": 1932, "site": "Morris" },
      { "yield": 32.06666, "variety": "Velvet", "year": 1932, "site": "Crookston" },
      { "yield": 32.23333, "variety": "Velvet", "year": 1932, "site": "Grand Rapids" },
      { "yield": 22.46667, "variety": "Velvet", "year": 1932, "site": "Duluth" },
      { "yield": 29.06667, "variety": "Trebi", "year": 1932, "site": "University Farm" },
      { "yield": 49.2333, "variety": "Trebi", "year": 1932, "site": "Waseca" },
      { "yield": 46.63333, "variety": "Trebi", "year": 1932, "site": "Morris" },
      { "yield": 41.83333, "variety": "Trebi", "year": 1932, "site": "Crookston" },
      { "yield": 20.63333, "variety": "Trebi", "year": 1932, "site": "Grand Rapids" },
      { "yield": 30.6, "variety": "Trebi", "year": 1932, "site": "Duluth" },
      { "yield": 26.43334, "variety": "No. 457", "year": 1932, "site": "University Farm" },
      { "yield": 42.2, "variety": "No. 457", "year": 1932, "site": "Waseca" },
      { "yield": 43.53334, "variety": "No. 457", "year": 1932, "site": "Morris" },
      { "yield": 34.33333, "variety": "No. 457", "year": 1932, "site": "Crookston" },
      { "yield": 19.46667, "variety": "No. 457", "year": 1932, "site": "Grand Rapids" },
      { "yield": 22.7, "variety": "No. 457", "year": 1932, "site": "Duluth" },
      { "yield": 25.56667, "variety": "No. 462", "year": 1932, "site": "University Farm" },
      { "yield": 44.7, "variety": "No. 462", "year": 1932, "site": "Waseca" },
      { "yield": 47, "variety": "No. 462", "year": 1932, "site": "Morris" },
      { "yield": 30.53333, "variety": "No. 462", "year": 1932, "site": "Crookston" },
      { "yield": 19.9, "variety": "No. 462", "year": 1932, "site": "Grand Rapids" },
      { "yield": 22.5, "variety": "No. 462", "year": 1932, "site": "Duluth" },
      { "yield": 28.06667, "variety": "Peatland", "year": 1932, "site": "University Farm" },
      { "yield": 36.03333, "variety": "Peatland", "year": 1932, "site": "Waseca" },
      { "yield": 43.2, "variety": "Peatland", "year": 1932, "site": "Morris" },
      { "yield": 25.23333, "variety": "Peatland", "year": 1932, "site": "Crookston" },
      { "yield": 26.76667, "variety": "Peatland", "year": 1932, "site": "Grand Rapids" },
      { "yield": 31.36667, "variety": "Peatland", "year": 1932, "site": "Duluth" },
      { "yield": 30, "variety": "No. 475", "year": 1932, "site": "University Farm" },
      { "yield": 41.26667, "variety": "No. 475", "year": 1932, "site": "Waseca" },
      { "yield": 44.23333, "variety": "No. 475", "year": 1932, "site": "Morris" },
      { "yield": 32.13333, "variety": "No. 475", "year": 1932, "site": "Crookston" },
      { "yield": 15.23333, "variety": "No. 475", "year": 1932, "site": "Grand Rapids" },
      { "yield": 27.36667, "variety": "No. 475", "year": 1932, "site": "Duluth" },
      { "yield": 38, "variety": "Wisconsin No. 38", "year": 1932, "site": "University Farm" },
      { "yield": 58.16667, "variety": "Wisconsin No. 38", "year": 1932, "site": "Waseca" },
      { "yield": 47.16667, "variety": "Wisconsin No. 38", "year": 1932, "site": "Morris" },
      { "yield": 35.9, "variety": "Wisconsin No. 38", "year": 1932, "site": "Crookston" },
      { "yield": 20.66667, "variety": "Wisconsin No. 38", "year": 1932, "site": "Grand Rapids" },
      { "yield": 29.33333, "variety": "Wisconsin No. 38", "year": 1932, "site": "Duluth" }
    ]
  },
  "transform": [
    {
      "joinaggregate": [{ "op": "median", "field": "yield", "as": "k" }],
      "groupby": ["variety"]
    },
    {
      "joinaggregate": [{ "op": "sum", "field": "yield", "as": "yieldSum" }],
      "groupby": ["variety"]
    }
  ],
  "encoding": {
    "x": { "field": "variety", "type": "ordinal", "sort": { "field": "k", "order": "descending" } }
  },
  "layer": [
    {
      "mark": "bar",
      "encoding": {
        "y": {
          "aggregate": "median",
          "field": "k",
          "type": "quantitative",
          "scale": { "zero": false }
        }
      }
    },
    {
      "mark": { "type": "line", "stroke": "red" },
      "encoding": {
        "x": { "field": "variety", "type": "ordinal", "sort": { "field": "k", "order": "descending" } },
        "y": {
          "field": "yieldSum",
          "type": "quantitative",
          "scale": { "zero": false },
          "sort": { "field": "k", "order": "descending" }
        }
      }
    }
  ],
  "resolve": {
    "scale": { "y": "independent" }
  }
}

Screen Shot 2021-03-02 at 1.31.47 PM
Screen Shot 2021-03-02 at 1.31.47 PM1029×781 48.1 KB

Brian

3

Hi @brianseeders

for above code it's working fine, but when I tried the same sorting with kibana_sample_data_ecommerce it was not sorting line graph ( In line graph we are showing a calculated value ).

code :

{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
 "width": 500,
  "height":700,
     data: {
    url: {
      %context%: true
      index: kibana_sample_data_ecommerce
      body: {
         size: 10
        _source: ["category", "taxless_total_price", "total_quantity", "day_of_week_i"]
        }
      }
      format: {property: "hits.hits"}
    },
    "transform": [
          
          {
            "joinaggregate": [{"op": "count", "field": "_source.category","as": "a_count"}],
            "groupby": ["_source.category"]
          }
          {
            "joinaggregate": [{"op": "sum", "field": "_source.taxless_total_price","as": "open_count"}],
            "groupby": ["_source.category"]
          },
          {
            "joinaggregate": [{"op": "sum", "field": "_source.total_quantity","as": "sent_count"}],
            "groupby": ["_source.category"]
          },
          {
            "joinaggregate": [{"op": "sum", "field": "_source.day_of_week_i","as": "bounce_count"}],
            "groupby": ["_source.category"]
          },
          {
            "calculate": "datum.sent_count-datum.bounce_count", "as": "aud_count"
          },
          {
            "calculate": "datum.sent_count", "as": "d_count"
          },
          {"filter": "datum.aud_count != 0"},
          {
            "calculate": "(datum.open_count/datum.aud_count*100)", "as": "b_count"
          }
      
    ],
   "encoding": {
    "x":{ "field": "_source.category", 
          "type": "nominal", 
          "title": "",
           "sort": { "field": "d_count", "order": "descending" },
          "axis":{"labelFontSize": 11.5, "titleColor": "#85C5A6"
        },
    },
   
  }
  "layer":[
    {
    "mark": {"opacity": 0.8, "type": "bar", "color": "#4973AB"},
    "encoding": {
       "y": {"field": "d_count",
          "type": "quantitative", 
          "title": "Count of Audience",
          "axis": {"titleColor":"#4973AB"},
           "sort": { "field": "d_count", "order": "descending" },
        },
      }
    },
    {
    "mark": { "type": "line", "point": {
      "filled": false,
      "fill": "white",
      "size":100,
      "color":"#F4D03F"
    },"size":3,"color":"#F4D03F"
   
    },
    "encoding": {
       "y": {"field": "b_count",
          "type": "quantitative", 
          "title": "Rate (%)",
          "axis": {"titleColor":"#F4D03F"},
          "sort": { "field": "d_count", "order": "descending" },
        },
      }
    }
  ],
  "resolve": {"scale": {"y": "independent"}} 
  }

output :

image
image909×412 19.4 KB

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.