So I want to display average case closing rate(for now in minute) on dashboard.

What I've done

So if I'm not wrong I can calculate the difference time like (closed date - created date)

Following is the query I've written

q = load "Cases_Data";
q = filter q by 'Status' in ["Closed"];
--q = foreach q generate toDate('ClosedDate_sec_epoch') as 'Closed Date', toDate('CreatedDate_sec_epoch') as 'Created Date',(('ClosedDate_sec_epoch' - 'CreatedDate_sec_epoch')/60) as 'Closed in Hour';
q = foreach q generate avg(('ClosedDate_sec_epoch'-'CreatedDate_sec_epoch')/60) as 'Closed in Hour';
--q = order q by 'Closed in Hour' asc;
q = limit q 2000;

It's actually calculating correctly but if I'm calculating an average it's throwing an error

Invalid aggregation function args avg('ClosedDate_sec_epoch' - 'CreatedDate_sec_epoch' / #60)

I've spend hours trying various things but it seems it's not working(maybe my knowledge is little as I'm new to this).

So please suggest me if this is correct way of doing it or is there any other way which is best than this.


I was able to get the difference/rate by adding filed with computeExpression in dataflow, following is the formula


the field is called case_close_rate and now I can calculate avg('case_close_rate') if I want to show it in dashboard/chart.

I don't know if this is proper solution to this or not so can anyone confirm this is good way of solving this?

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.