Percentage change from a prior lag

Grafana Dashboards
1
  • What Grafana version and what operating system are you using?
    Grafana v11.1.3
  • What are you trying to achieve?
    Have a table with Date and Value column which is a time series. Want to create another column which is to be a percentage change of the value from a prior lagged value ( one month or 6 months or 12 months). Not able to find a way to do this. The time series values are fetched using an api which has a JSON output, which is parsed and displayed in the table. This seems like a straightforward usecase and was expecting some transformation to handle it.
  • How are you trying to achieve it?
    The closest one is the Add field from calculation transformation, Reduce row mode and Difference percentage calculation. But this calculates the difference between the first and the last value which is not what is needed
  • What happened?
    No transformation available
  • What did you expect to happen?
    A transformation which can take a value and find difference from the same value with a specified prior lag value and express the change as a percentage
  • Can you copy/paste the configuration(s) that you are having problems with?
    NA
  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    NA
  • Did you follow any online instructions? If so, what is the URL?
    No documentation, link available to do this
2

welcome @kingeorge

best to do that in the data source query itself. what data source plugin are you using? infinity?

also can you share some sample json data

3

Welcome @kingeorge

If you do use Infinity Datasource, here is an example that shows how to calculate the change from a prior column. Note that your example will likely vary if your data structure returned in JSON is not the same as this other example.

1 Like
4

Thank you @yosiasz @grant2

The data source plugin used is JSON API and the output json (output truncated) looks like below

{"realtime_start":"2024-08-14","realtime_end":"2024-08-14","observation_start":"1600-01-01","observation_end":"9999-12-31","units":"lin","output_type":1,"file_type":"json","order_by":"observation_date","sort_order":"asc","count":931,"offset":0,"limit":100000,"observations":[{"realtime_start":"2024-08-14","realtime_end":"2024-08-14","date":"1947-01-01","value":"21.48"},{"realtime_start":"2024-08-14","realtime_end":"2024-08-14","date":"1947-02-01","value":"21.62"},{"realtime_start":"2024-08-14","realtime_end":"2024-08-14","date":"1947-03-01","value":"22.0"}

Followed the link on infinity example, as in the last comment, I wanted a running percentage change as a new table column which can be plotted as a time series.

5

not a valid json data. please vet it and post back

6

That is truncated data. It is fetching data from the fred api. The link is below for the whole data

[https://api.stlouisfed.org/fred/series/observations?series_id=CPIAUCSL&api_key=xxxxxxxxxxxxxxxxxxxxxxxx&file_type=json](https://API link)

1 Like
7

are you familiar with jsonata?

(
    $date_key := $.observations.{
        $replace($substring(date,0,7),'-',''): 
        {
            "date": date,
            "value": value
        }
    };
    
    $data := $.observations.{
        'date': date,
        'value': value,
        'lag': $lookup($date_key,"194710")
    };
    
)

take your json data you posted to https://try.jsonata.org/ and then try the following

image
image1200×835 48.8 KB

:fish: :fishing_pole_and_fish:

1 Like
8

Looks like JSONata is the way to go. The above code is promising. Thank you for the direction!

1 Like
9

Achieved the requirement with the following Jsonata code

$map(
  observations.value,
  function($v, $i, $a) {
    $i >= 12 ? (($number($v) - $number($a[$i - 12]))/$number($v))*100 : $number($v)
  }
)

Used 12 as the lag value. Using this code in https://try.jsonata.org/ might give an infinite loop error depending on the size of the data

2 Likes