Display data between multiple dates in kibana

[Yoav_Ben_Moha]

Hi,
On kibana version 7.4:

I am uploading into elastic daily index each toy sales transaction.
I need to display data in table visualization that contain summey of toys sales between several dates.

toy name  | (num of sales toys in the last hour)  |  (num of sales toys from last week) | (num of sales toys from last mounth)
A         |                      2                |                          20                |                 65              
B         |                      5                |                          25                |                 85              

Is there a way to do that ..... ?

Thanks

[Brandon_Kobel]

Hey @Yoav_Ben_Moha, assuming that each row above is an Elasticsearch document, this is going to be difficult. Instead, I'd recommend ingesting each sale as a separate document and allowing Elasticsearch to calculate the aggregate information. This will afford you much more flexibility when Visualizing your data in Kibana.

[Yoav_Ben_Moha]

Hi Bandon,

assuming that each row above is an Elasticsearch document, this is going to be difficult.

---> It is not.

Instead, I'd recommend ingesting each sale as a separate document and allowing Elasticsearch to calculate the aggregate information.

---> This is the case.

each time a toy is being seld a new decoument with the toy details is created.

I have a dashboard which shows details about toys sales from the last 60 minutes.
I was requested to add another vizualization that shows historical sales of top 5 products over time.

The tricky part is that i need to count documents from between DIFFERENT DATE RANGE and display them in the same table.
somting like the table bellow :

toy name  | (num of sales toys in the last hour)  |  (num of sales toys from last week) | (num of sales toys from last mounth)
A         |                      2                |                          20                |                 65              
B         |                      5                |                          25                |                 85          

Any Idea ?

[Brandon_Kobel]

@Yoav_Ben_Moha, gotcha. Thanks for the further explanation. I'm not aware of a way to generate this information using a single Visualization. Would you mind creating a feature request for this to be added?

You can always look at using Vega to create this visualization, as you have complete control over the query which is being executed against Elasticsearch and the resultant visualization. However, it is a rather low-level and complex visualization, so opening the feature request will allow us to accommodate this visualization in a more intuitive manner.

[Yoav_Ben_Moha]

Hi Brandon,
Thanks for your quick response.
Before creating new feature request or diving into vega , i have created simple test case
in RDBMS like model . I am sure there is a way to do it in Kibana 7.4 , its basic stuffe .....

define schema:

 create table sales
(item_id number,
sales_qty number,
insert_date date);

populate some data:

insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate);
insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate-5);
insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate-5);
insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate-4);
insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate-4);
insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate-10);
insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate-10);
insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate-12);
insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate-16);
insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate-16);
insert into sales (item_id,sales_qty,insert_date) values (1,1,sysdate-17);

Query over the data:

select item_id,
           ( select sum(sales_qty) from sales b where a.item_id = b.item_id and trunc(b.insert_date) = trunc(sysdate) ) today_sales,
           ( select sum(sales_qty) from sales b where a.item_id = b.item_id and trunc(b.insert_date) between sysdate-7 and sysdate-1 ) last_week_sales,
           ( select sum(sales_qty) from sales b where a.item_id = b.item_id and trunc(b.insert_date) between sysdate-14 and sysdate-1  ) two_week_sales,
           ( select sum(sales_qty) from sales b where a.item_id = b.item_id and trunc(b.insert_date) between sysdate-30 and sysdate-1 ) monthly_sales
from   sales a
group by item_id;

Stament results:

item_id   today_sales last_week two_week montly_sales
1	       1	         4	       7    	10

Thanks Again !