I have to let the user pick two dates and view a number of events for these dates.
I can easily make it for a fixed time interval, like this:
q = load "dataset"; q_A = filter q by date('date_Year', 'date_Month', 'date_Day') in ["1 day ago".."1 day ago"]; q_B = filter q by date('date_Year', 'date_Month', 'date_Day') in ["current day".."current day"]; result = group q_A by 'name' full, q_B by 'name'; result = foreach result generate coalesce(q_A.'name', q_B.'name') as 'name', sum(q_A.'numberOfEvents') as 'A', sum(q_B.'numberOfEvents') as 'B'; result = foreach result generate 'name', 'A', 'B', B-A as 'C'; result = order result by ('C' desc); result = limit result 2000;
I can also let the user view all data from a time range picked in a global filter:
q = load "dataset"; q = group q by ('name', 'date_Year', 'date_Month', 'date_Day'); q = foreach q generate 'name' as 'name', 'date_Year' + "~~~" + 'date_Month' + "~~~" + 'date_Day' as 'date_Year~~~date_Month~~~date_Day', sum('numberOfEvents') as 'sum_numberOfEvents'; q = order q by ('name' asc, 'date_Year~~~date_Month~~~date_Day' asc); q = limit q 10000;
But in this case, I cannot calculate the difference (delta) between the first and the last. And I need to see the data only for the first and last date.
I've tried to use
first() functions, but they don't work for dates.
Could anyone help me, please?