Split and group documents by two conditions in Kibana visualizations or Elasticsearch queries

[aza-zil (Ali)]

Let's say I have the following data in Elasticsearch:

• id : id of the document
• date : date of inserting the document
• changed : refers that the field 'working' has changed its status from last document inserted.
• working : current status.

I want to build a visualization that splits the data by the 'changed' and 'working' fields, as a start date is when the working changes to 'true' (so 'changed' also is true), and end date is when the working changes to 'false' (so 'changed' is true), like the following:

any ideas on how to do that?

[Stratoula_Kalafateli]

Hey! You could use the aggregation-based datatable visualization and use a split table sub-bucket with filters. Something like that:

[aza-zil (Ali)]

Thank you @Stratoula_Kalafateli for answering. Actually that won't solve the problem. Splitting the table won't allow me to get the two conditions together, it just will give me documents that have the required conditions.
What I want to do is the following:

[ghudgins (Graham Hudgins)]

just here to say I tried! this is a data modeling question - I don't think it's doable today in Kibana. I played with transforms and various min / max functions on dates but came up short. I think you probably need to interact with the API directly to construct your logic for this data. There's others on here who might have additional ideas.