How to calculate a avarage qty

[michak80]

Hello everyone!

I have following columns: 

OH_CREATION_DATE in format DD/MM/YYYY hh:mm:ss.
OH_ORDER_ID what is Order number 
I made hours using 'hour (OH_CREATION_DATE)' and the result is as below in table. 
then I use count (distinct OH_ORDER_ID) to count qty of orders by each hour (see below).

I need also avarage qty of orders created by each our. I tried 

Avg(Aggr(count(distinct OH_ORDER_ID), hour(OH_CREATION_DATE))) 

but it does not work. 

Any ideas how I can show such information? Thank you in advance

and it's not big issue but maybe you know how recieve hour in format hh (I mean 00, 01 instead of 0, 1) 

[Chanty4u · Accepted solution]

Try this 

LOAD 

    OH_ORDER_ID, 

    OH_CREATION_DATE, 

    Time(Floor(OH_CREATION_DATE, 1/24), 'HH') AS Order_Hour

FROM SourceTable;

In chart you can use below expression:

Avg(Aggr(Count(DISTINCT OH_ORDER_ID), Order_Hour))

[michak80]

Thanks you! Seems that it's solution 🙂