# Bar chart show a single bar of a month while different year

**URL:** <https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849>\
**Category:** Kibana\
**Created:** [November 22, 2018, 10:04am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849 "2018-11-22T10:04:46Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![vsingh](https://avatars.discourse-cdn.com/v4/letter/v/b4bc9f/32.png) [@vsingh](https://discuss.elastic.co/u/vsingh)\
**Post date:** [November 22, 2018, 10:04am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/1 "2018-11-22T10:04:46Z")

</div>

Hello,

I am new in kibana. i am using kibana 6.5.0.

I have a table which have column month, year and expenses\_value. i have 12 month values in month column (e.g January, February, March etc). i have data from November 2017 to November 2018.

I have use logstash to insert data into elastic search.  
now i am creating bar chart of this data.

i am taking expenses\_value on y-axis ( aggregation\>sum on expenses\_value). and month on x-axis (aggregation\>term on month field) and sub aggregation (aggregation\>term on year field).  
i have expenses\_value of November 2017 and November 2018. now on visualization, it is showing a single bar of November 2017 and November 2018 (combine values of November 2017 and November 2018, separated by different color in November month bar).

i want to create separate bar of each month and order by month with year.

please suggest.

Thanks.

---

<div class="post-metadata">

**Author:** ![Magnus\_Kessler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnus_kessler/32/42001_2.png) [@Magnus\_Kessler](https://discuss.elastic.co/u/Magnus_Kessler)\
**Post date:** [November 22, 2018, 10:27am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/2 "2018-11-22T10:27:01Z")

</div>

In your logstash pipeline, try to combine the year and month into the same field, which ideally would be mapped to the [`date` datatype](https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html) in the Elasticsearch index. You could then use a `date_histogram` in your visualisation.

To combine the fields, you could for example use `add_field` in the [`mutate` plugin](https://www.elastic.co/guide/en/logstash/current/plugins-filters-mutate.html#plugins-filters-mutate-add_field).

---

<div class="post-metadata">

**Author:** ![vsingh](https://avatars.discourse-cdn.com/v4/letter/v/b4bc9f/32.png) [@vsingh](https://discuss.elastic.co/u/vsingh)\
**Post date:** [November 22, 2018, 10:38am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/3 "2018-11-22T10:38:19Z")

</div>

> [@vsingh](#):
>
> expenses\_value

Thanks for your reply.  
In mysql database all fields are string type. i have convert two fields in integer.  
this is my logstash conf file:

```
input {

```

jdbc {  
jdbc\_driver\_library =\> "C:\Users\ELK\_Data\mysql-connector-java-8.0.13.jar"  
jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"  
jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/db\_name"  
jdbc\_user =\> "root"  
jdbc\_password =\> "mysql"  
statement =\> "SELECT \* FROM table\_name"  
type =\> "response"  
}  
}  
filter {  
mutate {convert =\> ["year", "integer"] }  
mutate {convert =\> ["expenses\_value", "integer"] }  
}  
output {  
elasticsearch {  
index =\> "total\_expenses\_transaction"  
document\_type =\> "%{type}"  
hosts =\> "localhost:9200"  
}  
stdout {}  
}

---

<div class="post-metadata">

**Author:** ![Magnus\_Kessler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnus_kessler/32/42001_2.png) [@Magnus\_Kessler](https://discuss.elastic.co/u/Magnus_Kessler)\
**Post date:** [November 22, 2018, 10:52am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/4 "2018-11-22T10:52:58Z")

</div>

Try something along these lines, before you convert `year` to `integer`:

```auto
filter {
  mutate {
    add_field {
      "my_date" => "%{year}-%{month}"
    }
  }
}

```

I'd also highly recommend to create an [index template](https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-templates.html) with the correct mappings for your data model.

---

<div class="post-metadata">

**Author:** ![vsingh](https://avatars.discourse-cdn.com/v4/letter/v/b4bc9f/32.png) [@vsingh](https://discuss.elastic.co/u/vsingh)\
**Post date:** [November 22, 2018, 11:25am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/5 "2018-11-22T11:25:46Z")

</div>

> [@vsingh](#):
>
> expenses\_value

Sir,  
I have change filter plugin to:  
filter {  
mutate {  
add\_field {  
"my\_date" =\> "%{year}-%{month}"  
}  
}  
mutate {convert =\> ["year", "integer"] }  
mutate {convert =\> ["expenses\_value", "integer"] }  
}  
i got this error:  
[2018-11-22T16:45:25,673][INFO][logstash.runner] Starting Logstash {"logstash.version"=\>"6.4.0"}  
[2018-11-22T16:45:26,331][ERROR][logstash.agent] Failed to execute action {:action=\>LogStash::PipelineAction::Create/pipeline\_id:main, :exception=\>"LogStash::ConfigurationError", :message=\>"Expected one of #, =\> at line 14, column 15 (byte 398) after filter {\n mutate {\n add\_field ", :backtrace=\>["C:/ELK/logstash/logstash-core/lib/logstash/compiler.rb:41:in `compile_imperative'", "C:/ELK/logstash/logstash-core/lib/logstash/compiler.rb:49:in `compile\_graph'", "C:/ELK/logstash/logstash-core/lib/logstash/compiler.rb:11:in `block in compile_sources'", "org/jruby/RubyArray.java:2486:in `map'", "C:/ELK/logstash/logstash-core/lib/logstash/compiler.rb:10:in `compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:157:in `initialize'", "C:/ELK/logstash/logstash-core/lib/logstash/pipeline.rb:22:in `initialize'", "C:/ELK/logstash/logstash-core/lib/logstash/pipeline.rb:90:in `initialize'", "C:/ELK/logstash/logstash-core/lib/logstash/pipeline\_action/create.rb:38:in `execute'", "C:/ELK/logstash/logstash-core/lib/logstash/agent.rb:309:in `block in converge\_state'"]}

---

<div class="post-metadata">

**Author:** ![Magnus\_Kessler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnus_kessler/32/42001_2.png) [@Magnus\_Kessler](https://discuss.elastic.co/u/Magnus_Kessler)\
**Post date:** [November 22, 2018, 12:18pm UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/6 "2018-11-22T12:18:50Z")

</div>

> [@Magnus\_Kessler](#):
>
> filter { mutate { add\_field { "my\_date" =\> "%{year}-%{month}" } } }

Sorry, this should've read

```auto
filter {
  mutate {
    add_field => {
      "my_date" => "%{year}-%{month}"
    }
  }
}

```

When posting error messages or code examples, could you please use `[code]...[/code]` blocks to make the output easier to read?

---

<div class="post-metadata">

**Author:** ![vsingh](https://avatars.discourse-cdn.com/v4/letter/v/b4bc9f/32.png) [@vsingh](https://discuss.elastic.co/u/vsingh)\
**Post date:** [November 26, 2018, 5:46am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/7 "2018-11-26T05:46:17Z")

</div>

Thanks Sir.

now separate bar showing for November 2017 and November 2018.  
Its showing in this order:  
2017-December  
2017-November  
2018-April  
2018-August  
2018-February  
....

Can i sort the month wise data.  
for example:  
2018-November  
2018-October  
2018-September  
..  
....  
2018-January  
2017-December  
2017-November

Thanks.

---

<div class="post-metadata">

**Author:** ![Magnus\_Kessler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnus_kessler/32/42001_2.png) [@Magnus\_Kessler](https://discuss.elastic.co/u/Magnus_Kessler)\
**Post date:** [November 26, 2018, 8:29am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/8 "2018-11-26T08:29:49Z")

</div>

Thanks for trying this out. However, I had assumed that the month would be a number between `01` and `12`, leading to combined values such as `2018-11`, which is easily converted into a date field value.

Is there any chance that you get a simple timestamp out of your SQL database?

---

<div class="post-metadata">

**Author:** ![vsingh](https://avatars.discourse-cdn.com/v4/letter/v/b4bc9f/32.png) [@vsingh](https://discuss.elastic.co/u/vsingh)\
**Post date:** [November 26, 2018, 10:25am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/9 "2018-11-26T10:25:10Z")

</div>

Sir,  
i have add another column req\_date. data type of this column is varchar in database which have date timestamp value.  
like this:  
For November-2017, req\_date= 2017-11-30 00:00:00.000  
For December-2017, req\_date= 2017-12-31 00:00:00.000  
For March-2018, req\_date= 2018-03-31 00:00:00.000

Thanks.

---

<div class="post-metadata">

**Author:** ![vsingh](https://avatars.discourse-cdn.com/v4/letter/v/b4bc9f/32.png) [@vsingh](https://discuss.elastic.co/u/vsingh)\
**Post date:** [November 26, 2018, 11:44am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/10 "2018-11-26T11:44:19Z")

</div>

Sir,  
I have used this date filter:  
date {  
match =\> ["req\_date", "ISO8601", "YYYY-MM-dd", "YYYY-MM-dd"]  
target =\> "req\_date"  
locale =\> "en"  
}

In database req\_date column having a value: 2017-11-30 00:00:00.000  
It is display on bar graph: November 30th 2017, 00:00:00.000  
Can i display only November 2017 on bar graph in visualization.

Thanks.

---

<div class="post-metadata">

**Author:** ![Magnus\_Kessler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnus_kessler/32/42001_2.png) [@Magnus\_Kessler](https://discuss.elastic.co/u/Magnus_Kessler)\
**Post date:** [November 26, 2018, 2:08pm UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/11 "2018-11-26T14:08:57Z")

</div>

Try using date histograms with a `monthly` interval.

---

<div class="post-metadata">

**Author:** ![vsingh](https://avatars.discourse-cdn.com/v4/letter/v/b4bc9f/32.png) [@vsingh](https://discuss.elastic.co/u/vsingh)\
**Post date:** [November 27, 2018, 5:57am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/12 "2018-11-27T05:57:30Z")

</div>

Sir,

Please put some cherries on top of that..

Can i display data values on top of each bar?

Thanks.

---

<div class="post-metadata">

**Author:** ![Magnus\_Kessler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnus_kessler/32/42001_2.png) [@Magnus\_Kessler](https://discuss.elastic.co/u/Magnus_Kessler)\
**Post date:** [December 3, 2018, 9:26am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/13 "2018-12-03T09:26:01Z")

</div>

> [@vsingh](#):
>
> Can i display data values on top of each bar?

If you mean that the number is displayed as text on top of the bar, this doesn't appear to be possible with the standard vertical bars visualisation. You can explore different settings in the _Metric and Settings_ and _Panel Settings_ tabs when you create the visualisation.

---

<div class="post-metadata">

**Author:** ![vsingh](https://avatars.discourse-cdn.com/v4/letter/v/b4bc9f/32.png) [@vsingh](https://discuss.elastic.co/u/vsingh)\
**Post date:** [December 3, 2018, 11:50am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/14 "2018-12-03T11:50:30Z")

</div>

Ok. Thank you Sir.

---

<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:** [December 31, 2018, 11:50am UTC](https://discuss.elastic.co/t/bar-chart-show-a-single-bar-of-a-month-while-different-year/157849/15 "2018-12-31T11:50:36Z")

</div>

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