SUM of Top Hits?


(Sjaak) #1

Hi,

I'm trying to graph the sum of top hits (cost) per company but I'm not getting the desired results.

The data looks like this

2018-1-19
Product: A | total_cost: 25 | company: A
Product: B | total_cost: 50 | company: A
Product: C | total_cost: 100 | company: B
Product: D | total_cost: 200 | company: B

2018-1-20
Product: A | total_cost: 50 | company: A
Product: B | total_cost: 100 | company: A
Product: C | total_cost: 200 | company: B
Product: D | total_cost: 400 | company: B

2018-1-21
Product: A | total_cost: 100 | company: A
Product: B | total_cost: 200 | company: A
Product: C | total_cost: 300 | company: B
Product: D | total_cost: 500 | company: B
Product: C | total_cost: 200 | company: B

The total_cost is the SUM of the total_cost so far so when I create my graphs I need to get the top hit for each product, so I have the latest values for each product, and then SUM the latest value for each product.

I then want to create a term value for the company so I get the SUM of the latest value for total_cost and a date histogram set to month so I get the SUM of the latest value for all products combined per month.

I'm failing to get the SUM part working. Is there any way to do this in Kibana?


(Marco Vettorello) #2

Hi @Sjaak01,
Before giving you an answer, I need to understand a bit more what you are looking for.

Can you describe me exactly, without any "kibana related" terms, what do you want to achieve and what is your raw data looks like (not only the one after the top hit for each product)?

Can you provide me what you expect to be the visualization/data using the example data you wrote?

Thanks


(Sjaak) #3

Hi,

My input data is month to date sales data.

A single record would look something like this.

Date: 2018-10-20
Product: productA
Cost: 20
Company: CompanyA

Of course CompanyA doesn't only buy productA but also productB, C, D etc. and of course there is more than one company buying products.

The data is delivered in a month to date style of way so the cost would be the total cost for the month. This data is delivered daily.

I want to know A) the total cost for product X B) the total cost for all product, C) the total cost per product per company and D) the total cost of all products per company.

The get the correct data for some of this I would need to get the last record per month and sum that up. I'm currently working around this by rolling my indices monthly and assigning product_id's so that currently data gets overwritten and the last records should be for example 2018-xx-31. If I do it like this getting the sum of top hits gives me the data I'm looking for but if I keep the historical data then it doesn't work as obviously too much stuff gets summed.

The downside is I lose the ability to view historical data within a month.


(system) #4

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