To write a custom code for visualization

[Ritikapawar]

I'm writing a custom code in kibana (vega). In which I want show the the total working hours of employe by calculating diffrence from two available fields like (out time - Intime).

how can I get that in vega by writing a script. (without creating a total count of working hours field in csv file).

{
  "$schema": "https://vega.github.io/schema/vega-lite/v2.json",
  "data": {
    "url": {
      "index": "data2",
      "%context%": true,
      "body": {"size": 10000}
        "aggs": {
        "Working hours": {
            "sum": {
                "script": {
                    "source": "doc['O-time'].value - doc['I-time'].value"
                }
            }
        }
    }
    },
    "format": {"property": "hits.hits"}
  },
  "mark": "bar",
  "encoding": {
    "tooltip": {"field": "_source.ID", "type": "ordinal"},
    "y": {"aggregate":"sum","field": "Working hours", "type": "quantitative"},
    "x": {"field": "_source.NAME", "type": "ordinal"}
  }
}

but using this code I'm able to get names of employe on x-axis but it shows count of working hours as zero/undefined on y axis.

I want the output in this format but here I've used a existing field (total working hours) from the CSV file.

[aaron-nimocks]

I would just query the data as normal without a script and then use a calculate transform to create a new total field that is based on your required calculations. Would be something like the below.

  "transform": [
    {"calculate": "datum.O-time - datum.I-time", "as": "total"}
  ]

[Ritikapawar]

{
    "$schema": "https://vega.github.io/schema/vega-lite/v2.json",
    "data": {
      "url": {
        "index": "data2",
        "%context%": true,
        "body": {"size": 10000}
      },
      "format": {"property": "hits.hits"},
      "transform": [
      {"calculate": "datum.O-time - datum.I-time", "as": "total"}
    ]
    },
    "mark": "bar",
    "encoding": {
      "tooltip": {"field": "_source.ID", "type": "ordinal"},
       "y": {"field": "total", "type": "ordinal",axis:{title: "working hours of employee"}
       },
      
      "x": {"field": "_source.NAME", "type": "ordinal"}
    }
  }

In this way ?

[aaron-nimocks]

Yes, something like that. I don't know what your data looks like so it's hard to tell but that's the idea.

Bring in all the data then do a transform to create a new field based off 2 others.

[Ritikapawar]

ID,NAME,I-time,O-time,Date
1,Amey,10,18,01/02/2022
2,Ritika,10,17,01/02/2022
3,Shubham,10,18,01/02/2022
4,Neha,10,17,01/02/2022
5,Satish,10,17,01/02/2022

My CSV file is in this format.

[Ritikapawar]

Did the process as we discussed .but still it shows the count of working hours as undefined,
but showing the field with names on x-axis correctly.
Is there anything that I'm missing??

[aaron-nimocks]

To make it easier can you provide your spec after following these steps?

[Ritikapawar]

{
  "$schema": "https://vega.github.io/schema/vega-lite/v2.json",
  "data": {
    "format": {
      "property": "hits.hits"
    },
    "transform": [
      {
        "calculate": "datum.O-time - datum.I-time",
        "as": "total"
      }
    ],
    "url": {
      "index": "data2",
      "body": {
        "size": 10000,
        "query": {
          "bool": {
            "must": [],
            "filter": [],
            "should": [],
            "must_not": []
          }
        }
      }
    },
    "values": {
      "took": 0,
      "timed_out": false,
      "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
      },
      "hits": {
        "total": 5,
        "max_score": 1,
        "hits": [
          {
            "_index": "data2",
            "_type": "_doc",
            "_id": "n58eyYEBLNZ1PJec19vy",
            "_score": 1,
            "_source": {
              "I-time": 10,
              "@timestamp": "2022-01-02T00:00:00.000+05:30",
              "O-time": 18,
              "ID": 1,
              "Date": "01/02/2022",
              "NAME": "Amey"
            }
          },
          {
            "_index": "data2",
            "_type": "_doc",
            "_id": "oJ8eyYEBLNZ1PJec19vy",
            "_score": 1,
            "_source": {
              "I-time": 10,
              "@timestamp": "2022-01-02T00:00:00.000+05:30",
              "O-time": 17,
              "ID": 2,
              "Date": "01/02/2022",
              "NAME": "Ritika"
            }
          },
          {
            "_index": "data2",
            "_type": "_doc",
            "_id": "oZ8eyYEBLNZ1PJec19vy",
            "_score": 1,
            "_source": {
              "I-time": 10,
              "@timestamp": "2022-01-02T00:00:00.000+05:30",
              "O-time": 18,
              "ID": 3,
              "Date": "01/02/2022",
              "NAME": "Shubham"
            }
          },
          {
            "_index": "data2",
            "_type": "_doc",
            "_id": "op8eyYEBLNZ1PJec19vy",
            "_score": 1,
            "_source": {
              "I-time": 10,
              "@timestamp": "2022-01-02T00:00:00.000+05:30",
              "O-time": 17,
              "ID": 4,
              "Date": "01/02/2022",
              "NAME": "Neha"
            }
          },
          {
            "_index": "data2",
            "_type": "_doc",
            "_id": "o58eyYEBLNZ1PJec19vy",
            "_score": 1,
            "_source": {
              "I-time": 10,
              "@timestamp": "2022-01-02T00:00:00.000+05:30",
              "O-time": 17,
              "ID": 5,
              "Date": "01/02/2022",
              "NAME": "Satish"
            }
          }
        ]
      }
    }
  },
  "mark": "bar",
  "encoding": {
    "tooltip": {
      "field": "_source.ID",
      "type": "ordinal"
    },
    "y": {
      "field": "total",
      "type": "ordinal",
      "axis": {
        "title": "working hours of employee"
      }
    },
    "x": {
      "field": "_source.NAME",
      "type": "ordinal"
    }
  },
  "config": {
    "range": {
      "category": {
        "scheme": "elastic"
      }
    },
    "mark": {
      "color": "#54B399"
    },
    "title": {
      "color": "#343741"
    },
    "style": {
      "guide-label": {
        "fill": "#69707d"
      },
      "guide-title": {
        "fill": "#343741"
      },
      "group-title": {
        "fill": "#343741"
      },
      "group-subtitle": {
        "fill": "#343741"
      }
    },
    "axis": {
      "tickColor": "#eef0f3",
      "domainColor": "#eef0f3",
      "gridColor": "#eef0f3"
    },
    "background": "transparent"
  },
  "width": "container",
  "height": "container",
  "autosize": {
    "type": "fit",
    "contains": "padding"
  }
}

Have a look

[aaron-nimocks]

Took me a bit but the reason this doesn't work is you have a - in your field name and it was translating that to a minus. To avoid that you can write it like this.

  "transform": [
    {
      "calculate": "datum._source['O-time'] - datum._source['I-time']",
      "as": "total"
    }
  ]

See in Editor.

[Ritikapawar]

Tried with the solution above, but still it shows working hours as undefined.

[aaron-nimocks]

The transform for Vega usually goes within the data object. The transform for vega-lite usually happens outside the data object. See the example link I posted in the post above to see how I did it.