Aggregate between 2 Index and vice versa

[Datakids (Thorben)]

ELK Stack Version 6.6.0

Hi all,

I'm struggle with following case and hope you can help me to understand how it is working technically.

I have 2 Index in one Node
1 named NaO
1 named BaO

NaO held ProductGroups, ProductTypes and their TotalTurnovers
BaO held the same ProductTypes but additional information like: parParameters and parValues

What I try in Kibana:

Build 2 Visuals and add them into a Dashboard:

Pie with BaO:
Inner Circlce: ProductTypes (text)
Middle: parParameters (text)
Out: parValues (text)

Horizontal Bars with NaO:
Y Axis = TotalTurnover (float)
X Axis = ProductTypes (text)
Split Series: ProductGroups (text)

Now:
If I select the ProductTypes (Inner) in Pie / BaO
Turnover in NaO now aggregated and show me the the right value = works fine

If I select the NaO ProductTypes the Pie / BaO is also filtering the right values = works fine

Bu if I select in Pie / BaO the parParameters (middle) or parValues (out)
no TotalTurnover in NaO is showing / noresults.

Why?
And what do I have to do to get the Turnovers when "not" select over ProductTypes if that is technically possible.

Data BaO Pie Request:

{
  "aggs": {
    "4": {
      "terms": {
        "field": "ProductTypes.keyword",
        "size": 10,
        "order": {
          "_count": "desc"
        }
      },
      "aggs": {
        "1": {
          "cardinality": {
            "field": "ProductGroups.keyword"
          }
        },
        "3": {
          "terms": {
            "field": "parParameters.keyword",
            "size": 10,
            "order": {
              "_count": "desc"
            }
          },
          "aggs": {
            "1": {
              "cardinality": {
                "field": "ProductGroups.keyword"
              }
            },
            "2": {
              "terms": {
                "field": "parValues.keyword",
                "size": 10,
                "order": {
                  "_count": "desc"
                }
              },
              "aggs": {
                "1": {
                  "cardinality": {
                    "field": "ProductGroups.keyword"
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  "size": 0,
  "_source": {
    "excludes": []
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {},
  "docvalue_fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "@timestamp": {
              "gte": 1549807055871,
              "lte": 1554987455871,
              "format": "epoch_millis"
            }
          }
        }
      ],
      "filter": [
        {
          "match_all": {}
        },
        {
          "match_all": {}
        }
      ],
      "should": [],
      "must_not": [
        {
          "match_phrase": {
            "parParameters.keyword": {
              "query": "testset"
            }
          }
        }
      ]
    }
  }
}

Data NaO Bar Request:

{
  "aggs": {
    "4": {
      "terms": {
        "field": "ProductTypes.keyword",
        "size": 5,
        "order": {
          "1": "desc"
        }
      },
      "aggs": {
        "1": {
          "sum": {
            "field": "TotalTurnover"
          }
        },
        "3": {
          "terms": {
            "field": "ProductGroups.keyword",
            "size": 5,
            "order": {
              "1": "desc"
            }
          },
          "aggs": {
            "1": {
              "sum": {
                "field": "TotalTurnover"
              }
            }
          }
        }
      }
    }
  },
  "size": 0,
  "_source": {
    "excludes": []
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {},
  "docvalue_fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [
        {
          "match_all": {}
        },
        {
          "range": {
            "@timestamp": {
              "gte": 1549807055871,
              "lte": 1554987455871,
              "format": "epoch_millis"
            }
          }
        }
      ],
      "filter": [
        {
          "match_all": {}
        }
      ],
      "should": [],
      "must_not": []
    }
  }
}

Thanks and Brdgs
Thorben

[Marius_Dragomir]

With the way you have your data set up this is how it works for now. Filtering on a piece of the pie chart in a dashboard will add a global filter for all the visualizations in that dashboard.
What i would do is add the data from the NaO index to the BaO index, to each corresponding document. This way you will have all the fields from those 2 visualizations available in one index, so the filter will not show no results now.

[LeeDr (Lee Drengenberg)]

You might also want to try turning this setting on in Management > Advanced Settings;

[Datakids (Thorben)]

Hi Marius,
thanks for your reply.
I tried this way but looks like I must flattened the datasource a bit more to achive this result.

Thanks and Brgds