I have a set of transactions with date and timestamp fields.
I wish to calculate arrival rate (no. of transactions per minute) for each hour.
What I have is a calculated field:
TRAN_COUNT = COUNTD(Order_key);
How do I specify an aggregate calculation: Number of minutes = MINUTES(Max(timestamp) - Min(Timestamp)
so I can then have ARRIVAL_RATE = TRAN_COUNT / NUM_MINUTES
MINUTES(Max(timestamp) - Min(Timestamp)
This can be calculated this way:
.
DATEDIFF('minute', MIN([Timestamp]), MAX([Timestamp]))
.
Just remember that DATEDIFF cuts off the seconds when calculating, such as counts:
09:03:59 to 09:04:00 as 1 minute (but only 1 second) and
09:03:00 to 09:03:59 as 0 minute (but 59 seconds)
Ps. It is easier to attract helpers when a packaged workbook is attached: Sending a Packaged Workbook
