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 min() and first() functions, but they don't work for dates.

Could anyone help me, please?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.