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 !