Calculate and visualize from two indices

In my use case
One index is sales history like:

BranchID    ItemID   Prices  Sale_date
 PA1100     O18740     75    4/11/2018
 PA6840     P25400    100    4/11/2018
   ..         ..       ..       ..

And the other is base line (documentation) like:

BranchID    Region   County    Location       Goal
 PA1100      North    City A   Street I,45   200000
 PA2001      East     City B   Street J,87   155000
 PA2045      East     City C   Street K,22   180000
   ..         ..        ..         ..         ..

Both indices have more than ten thousands of documents.
So,How can I create a percentage visualizer by using sum of prices in sales history divided by sum of goal in base line.

Elastic Version : 6.2.4
Thanks for any help in advance.

Canvas is being created to solve problems like these. It allows you to build visualizations from SQL statements that allow you to perform joins. I would recommend you try it out for this problem set.

If canvas does not work for you then, only timelion and visual builder offer support for multiple indices. You could create an expression that looks something like this in timelion .es(index="sales_history",timefield="yourTimefield",metric=sum:price).divide(.es(index="base_line",timefield="yourTimefield",metric=sum:goal))

In base_line index have no timefield that cause no result show on timelion.
I will learn about canvas from your suggestion.

@Nathan_Reese
Canvas doesn't work for me because It is not a dynamic visualizer.
any ideas to solve this problem.

What do you mean by "dynamic visualize"? What problems are you having with canvas?

I mean chart on visualize can filter by click on themselves.
How can I link or combine the indices. That make me use data from two indices in one visualize.

I would recommend opening an enhancement issue for you feature request

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