Use of "Terms" as "Buckets" with "Parent Pipeline Aggregations" e.g. "Derivative"

[malsioufi]

Hi,
I am working on some visualization to track number of issues (let's say bugs) over different versions of a system, and I am aiming to build a visualization with Kibana that uses the release name as X-axis and the diff. of number of issues between releases on the Y-axis. Something like the following. (Blue is number of issues, and orange is the changes I am interested in)

The versions are named after fruits and vegetables in an alphabetic order starting from A.

I have tried to use the visualization functionality in Kibana, but when I choose the Aggregation (Y-axis) to be "Derivative" of "Sum" of issues, I am not able to choose the Buckets (X-axis) as "Terms" to choose the version field which is stored as string, and I am getting the error message (Last bucket aggregation must be "Date Histogram" or "Histogram" when using "Derivative" metric aggregation.)

Is there a way or a work-around to achieve this goal? Because from the version's perspective, they do represent some chronological order but not in a date format.

[Marius_Dragomir]

Hello,

Yes, that is a limitation coming from Elasticsearch, the Derivative or Serial Diff aggregation only works on Histograms (be it normal or Date), mostly due to limitations linked to edge cases.
The only solution I see would be to create a scripted field that assigns numeric values to each version name and then do the Derivative agg on that new scripted field. What would be great if annotations were available in order to see the actual version name, but that's not possible yet. It's an open issue for improvement in Lens: https://github.com/elastic/kibana/issues/62277
And TSVB won't work for you since you don't have time series data.

[malsioufi]

Thanks for you answer Marius, that's a shame
Waiting to see this functionality implemented at some time soon.

[malsioufi]

Thank you @Marius_Dragomir for the scripted field tip! I solved the issue by creating a scripted field as you suggested so it has a numeric value for each version, so its script looks like this:

if(doc['release.keyword'].value == "Apple"){
    return 0
}
else{
    if(doc['release.keyword'].value == "Banana"){
        return 1
    } 
    else{
        if(doc['release.keyword'].value == "Banana2"){
            return 2
        }
        else{
			if(doc['release.keyword'].value == "Coconut"){
				return 3
			}
			else{
				return 4
			}
        }
    }
}

Then fixed its Format to be a lookup table

This is a very ugly work around but it works since I have a limited number of terms, so far.

[Marius_Dragomir]

Indeed, it's not really scalable sadly. Glad it helped you!