% by breaking subgroup

Elastic Stack Elasticsearch
1

I have following record

month   client  minute  
1/1/25   A      100     
1/1/25   B      100    
 
2/1/25   A      100     
2/1/25   B      100

I want to setup table vz by month aggregation

1/1/25  A  100  25%
1/1/25  B  100  25%
2/1/25  A  100  25%
2/1/25  B  100  25%
--------------------
           400

first column is month aggregation

second column is client

third colum is formula is sum(minutes)

last column is (sum(minutes) / overall_sum(sum(minutes)))

but when I do this each % shows 100%. what am I doing wrong?

It seems to be doing overall sum for only month for that client rather then full sum of everything

2

if I remove the month aggregation (first column) it works. But I want multiple month to select for better visibility.

3

hmm after lot of testing I still can’t figure out

4

Record?

Is this one document? More than one, 4 maybe? Result from some aggregation?

Most "how do I achieve this" questions are actually "how do I achieve this with a given set of data". It helps significantly if you also share the actual data (or a representative sample)!

1 Like
5

Hello @elasticforme

I need to try but maybe we should create a keyword field from date field so that the date histogram bucket is not applied month wise for the total %

Thanks!!

6

@RainTown yes this is four difference document. I just put this for simple representation.

@Tortoise you are right if I remove month (date) histogram aggregation it works. but when I have this month aggregation it does not works.

7

Hello @elasticforme

Yes, if we create a date_k keyword field which is derived from date field than it is possible :

image
image1081×252 14 KB

Created a runtime field date_keyword : 

if (doc['month'].size() > 0) {
    emit(doc['month'].value.toString().substring(0,7));
}


Data Set used : 

PUT minutes_data/_doc/1
{
  "month": "2025-01-02",
  "client": "A",
  "minute": 200
}

PUT minutes_data/_doc/2
{
  "month": "2025-01-01",
  "client": "B",
  "minute": 100
}

PUT minutes_data/_doc/3
{
  "month": "2025-02-01",
  "client": "A",
  "minute": 100
}

PUT minutes_data/_doc/4
{
  "month": "2025-02-01",
  "client": "B",
  "minute": 100
}


PUT minutes_data/_doc/5
{
  "month": "2025-01-01",
  "client": "A",
  "minute": 100
}

Thanks!!