Problem in sorting dual axis graph

Pranusha119 · February 26, 2021, 2:11pm

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 :

Expected Output :

{
  "$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"}
        }
  
}

brianseeders · March 2, 2021, 6:32pm

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" }
  }
}

Brian

Pranusha119 · March 24, 2021, 6:37am

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 :

system · April 21, 2021, 6:37am

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

Topic		Replies	Views
Y-Axis not sorting Ascending or Descending on Vega-Lite Kibana vega	4	1474	August 21, 2019
Sorting Vega Kibana vega	3	365	July 25, 2022
Custom sort sequence is not working when using in vega-lite Kibana vega	1	549	March 3, 2022
Help with window sort in vega Kibana vega	4	872	November 12, 2021
Kibana Canvas Line Chart Y-axis Sorting Kibana canvas	1	90	June 1, 2024

Problem in sorting dual axis graph

Related topics