# Aggregate ElasticSearch data and visualize into Gantt chart

**URL:** <https://discuss.elastic.co/t/aggregate-elasticsearch-data-and-visualize-into-gantt-chart/168717>\
**Category:** Kibana\
**Created:** [February 17, 2019, 8:19am UTC](https://discuss.elastic.co/t/aggregate-elasticsearch-data-and-visualize-into-gantt-chart/168717 "2019-02-17T08:19:22Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ozmhsh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ozmhsh/32/49129_2.png) [@ozmhsh](https://discuss.elastic.co/u/ozmhsh)\
**Post date:** [February 17, 2019, 8:19am UTC](https://discuss.elastic.co/t/aggregate-elasticsearch-data-and-visualize-into-gantt-chart/168717/1 "2019-02-17T08:19:22Z")

</div>

First of all, I think this question is related to Kibana, let me know if I am wrong:-)

My question is that I want to aggregate my ElasticSearch Index data and then visualize it into the Gantt chart, I don't know if I am on the right path for solving the problem.

Here is the example of my data:  
[{  
ID: ID1,  
StartEndFlag: Start  
Date: 2011-01-01  
},  
{  
ID: ID1,  
StartEndFlag: End  
Date: 2011-01-02  
},{  
ID: ID2,  
StartEndFlag: Start  
Date: 2011-01-03  
},  
{  
ID: ID2,  
StartEndFlag: End  
Date: 2011-01-04  
}]

and I want to display it as the Gantt chart with x-axis being date time, and y-axis being ID1 and ID2, and the horizontal bar will be displayed from 2011-01-01 to 2011-01-02 for ID1 and 2011-01-03 to 2011-01-04 for ID2

What I am thinking about the steps as follows:

1. Aggregate the ElasticSearch data using the query in Kibana into something like below:  
{ID: ID1  
StartDate: 2011-01-01  
EndDate: 2011-01-02  
}
2. Using Vega component to display as in this thread:  
[Gantt Chart timeline issue with Vega visualization](https://discuss.elastic.co/t/gantt-chart-timeline-issue-with-vega-visualization/153686)

Note: The reason I did not want to aggregate the data was to keep the original timestamp (which I did not add into my example above) and I want to generate other charts using the raw data.

What I could think of possibly is that maybe I don't need to aggregate the data and some functionalities exist?

Please advise, thanks a lot in advance!

---

<div class="post-metadata">

**Author:** ![bhavyarm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bhavyarm/32/22392_2.png) [@bhavyarm](https://discuss.elastic.co/u/bhavyarm)\
**Post date:** [February 18, 2019, 8:27pm UTC](https://discuss.elastic.co/t/aggregate-elasticsearch-data-and-visualize-into-gantt-chart/168717/2 "2019-02-18T20:27:45Z")

</div>

Vega should definitely help here. Did you see if it helps?

Thanks,  
Bhavya

---

<div class="post-metadata">

**Author:** ![ozmhsh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ozmhsh/32/49129_2.png) [@ozmhsh](https://discuss.elastic.co/u/ozmhsh)\
**Post date:** [February 19, 2019, 6:34pm UTC](https://discuss.elastic.co/t/aggregate-elasticsearch-data-and-visualize-into-gantt-chart/168717/3 "2019-02-19T18:34:46Z")

</div>

Vega is also new to me, and yesterday I found it can do the pivot but haven't tried yet.

Will try today, but if you have an example, it will be really helpful.

---

<div class="post-metadata">

**Author:** ![ozmhsh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ozmhsh/32/49129_2.png) [@ozmhsh](https://discuss.elastic.co/u/ozmhsh)\
**Post date:** [February 19, 2019, 10:57pm UTC](https://discuss.elastic.co/t/aggregate-elasticsearch-data-and-visualize-into-gantt-chart/168717/4 "2019-02-19T22:57:08Z")

</div>

I tried below, but no luck, can you help?

Vega code:

data:{  
name:"tables",  
values:[  
{  
"country":"Norway",  
"type":"gold",  
"count":14  
},  
{  
"country":"Norway",  
"type":"silver",  
"count":14  
},  
{  
"country":"Norway",  
"type":"bronze",  
"count":11  
},  
{  
"country":"Germany",  
"type":"gold",  
"count":14  
},  
{  
"country":"Germany",  
"type":"silver",  
"count":10  
},  
{  
"country":"Germany",  
"type":"bronze",  
"count":7  
},  
{  
"country":"Canada",  
"type":"gold",  
"count":11  
},  
{  
"country":"Canada",  
"type":"silver",  
"count":8  
},  
{  
"country":"Canada",  
"type":"bronze",  
"count":10  
}  
],  
"transform":[  
{  
"type":"pivot",  
"groupby":[  
"country"  
],  
"field":"type",  
"value":"count"  
}  
]  
}

Here is the output from Chrome Console:

[  
{  
"country": "Norway",  
"type": "gold",  
"count": 14  
},  
{  
"country": "Norway",  
"type": "silver",  
"count": 14  
},  
{  
"country": "Norway",  
"type": "bronze",  
"count": 11  
},  
{  
"country": "Germany",  
"type": "gold",  
"count": 14  
},  
{  
"country": "Germany",  
"type": "silver",  
"count": 10  
},  
{  
"country": "Germany",  
"type": "bronze",  
"count": 7  
},  
{  
"country": "Canada",  
"type": "gold",  
"count": 11  
},  
{  
"country": "Canada",  
"type": "silver",  
"count": 8  
},  
{  
"country": "Canada",  
"type": "bronze",  
"count": 10  
}  
]

Not the expected as in this url:

> **[Pivot Transform](https://vega.github.io/vega/docs/transforms/pivot/)**
>
> Vega - A Visualization Grammar. Vega is a visualization grammar, a declarative format for creating, saving, and sharing interactive visualization designs. With Vega, you can describe the visual appearance and interactive behavior of a visualization...

```auto
[
  {"country": "Norway", "gold": 14, "silver": 14, "bronze": 11},
  {"country": "Germany", "gold": 14, "silver": 10, "bronze": 7},
  {"country": "Canada", "gold": 11, "silver": 8, "bronze": 10},
]

```

What did I do wrong?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 19, 2019, 10:57pm UTC](https://discuss.elastic.co/t/aggregate-elasticsearch-data-and-visualize-into-gantt-chart/168717/5 "2019-03-19T22:57:14Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.