How to draw pie chart with sum of two columns

Elastic Stack Kibana
1

Hey guys !

I've got a CSV which is imported into elasticsearch. It looks like :

day;brushing;haircut;waxing hair removal;makeup
Monday;150;350;0;50
Tuesday;130;450;0;120
Wednesday;40;250;60;120
Thursday;78;150;80;0
Friday;65;120;70;50
Sunday;250;540;130;90

And thanks to Kibana I want to do a pie chart which represent the repartition between (sum of haircut + sum of brushing) and (sum of waxing hair removal + sum of makeup).

Here :

  • Sum of brushing : 713.

  • Sum of haircut : 1860.

  • Sum of these two values : 2573. (76.97%).

  • Sum of waxing hair removal :340.

  • Sum of makeup : 430

  • Sum of these two values : 770 (23.03%).

And now I want a graph with these two bold values like this example :
image

Could you help me ? I Can't draw my pie chart ..

Ps : With metric, I just succed to have "2573" and "770" with :

Sum aggregation and JSON input : {"script" : "doc['brushing'].value + doc['haircut'].value"}
and
Sum aggregation and JSON input : {"script" : "doc['waxing hair removal'].value + doc['makeup'].value"}

But i'm still unable to have these two values on my pie chart

1 Like
3

The splits in a pie chart are based on elasticsearch bucket aggregations, so each split is based on a group of documents. In order to split the pie chart the way you want, you need a way to slice up and group your documents based on the type of service that was provided. There's no way to split a pie chart on multiple metrics that are based on the same underlying group of documents.

You'd have more flexibility if you structure your data model a little differently. Try splitting each line item into its own document, so each document looks something like this:

{
  day: "Monday",
  service: "brushing",
  total: 150
}

{
  day: "Monday",
  service: "haircut",
  total: 350
}

{
  day: "Monday",
  service: "waxing hair removal",
  total: 0
}

... etc

Now, in your pie chart configuration you can do a filters aggregation. For one filter do service:(brushing OR haircut) and for the other do service:("waxing hair removal" OR makeup). Now you've got two buckets, one with documents representing hairdressing benefits, the other representing aesthetic benefits. Now in the metrics section of the pie chart configuration you just need to do a sum on the total field. That should generate the visualization you're looking for!

4

Hey Bargs :slight_smile:

Ok I'll try it asap and i'll tell u if I got problems

Thx for answer !

5

I have just returned to work on this project and I do not understand where I need to modify my document to obtain:

{
day: "Monday",
service: "brushing",
total: 150
}

Directly in my logstash pipeline or elsewhere ?

Thx

6

I up this topic cause I really need help about that :slight_smile:

Thank you in advance @Bargs

7

Since your data is just coming from a CSV, I would write a script or use a spreadsheet application or something like that to get the CSV into the required format before ingesting into ES.

8

But what do you mean about "required format" ?

9

The format I described in my first post. You'd transform your csv so that it has three columns, day, service, and total.

10

@Bargs

Thank you ! It solves my problem

11

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