Applying formulas to a Visualization


I’m trying to use Opensearch to monitor my solar panels at home. I have uploaded some sample data from which I want to create some statistics.
I have 2 values I want to display: the injected power (from my solar panels) and the consumed power (from the grid)
The problems that I have are:

  • the initial values are in kWh, and I wan to have them in Wh (x1000)
  • I want to display the consumed power as negative in my graph.
  • My timestamp (x-axis) does not contain the date, only the time, while my field has them both

I am using Elastic search at work, and I’m looking for similar features in opensearch, meaning that in this case I am looking to apply a formula to the metrics that I want to graph.
In elastic Stack, I edit the lens, and when I edit the vertical axis, I have a formula tab next to the quick functions.
In Opensearch however, I cannot find such tabs. I have been looking under “data”, “metrix & axes” and “panel settings”.

I’ve also been looking in the documentation, but I could not find much help on the Visualization topic.

Also to be said is that I’m not a black belt in Elastic neither, I’m just starting to learn :wink:

Can you please help me with:

  • Applying the formulas to my graphs
  • Fixing the timestamp format in the X-axis
  • Finding some good documentation on the visualization of my data

Thanks a lot!


Hi Wim,

We don’t yet support easy metric formulas in visualizations, but what you’re looking for is still possible. The general idea is to create “scripted fields” for each field you want to format, calculate, or otherwise transform.

  1. Go to Index Pattern management (/app/management/opensearch-dashboards/indexPatterns)
  2. Click on the Index Pattern with your solar data
  3. Click on the “Scripted Fields” tab
  4. Click “Add a scripted field” button
  5. Fill in the form with your field name and format
  6. Click “Get help with the syntax and preview the results of your script.” link
  7. Click “Preview results” tab to validate that the script is working as intended
  8. Click “Create field” button
  9. Now you should be able to access and use the new scripted field in visualizations

Here’s an example of what step 5 might look like, if your consumed power field was named consumed_power: