Avg days between two date columns during specific time period

[sloman123]

Hi

Lets say i have the table below. How do I do to show average days between order and delivery date during 2018 for Group A.

Orderdate	Deliverydate	Group
20180101	20180201	A
20180101	20180205	B
20190101	20190201	A
20190101	20190203	A
20200101	20200201	B
20200101	20200202	A

Thanks in advance

[sergio0592]

Hi,

Try with :

=avg({<Group={"A"}>}  aggr(date#(Deliverydate,'YYYYMMDD')-date#(Orderdate,'YYYYMMDD'),Deliverydate,Orderdate,Group))

[sloman123]

Thanks,

Got this to work

=avg({<[Group]={"A"}>}aggr(([Deliverydate])-([Orderdate]),[Deliverydate],[Orderdate],[Group]))

If I want to use yet another date to specify the timeperiod, how do i do that. I dont get it to work.

Lets say i have the table below,

J-date	Orderdate	Deliverydate	Group
2020-01-01	2020-01-01	2020-01-05	A
2020-02-02	2020-02-01	2020-02-03	A
2020-02-05	2020-01-05	2020-02-10	A
2019-01-01	2019-01-01	2019-01-09	A
2019-01-03	2019-01-01	2019-01-05	A
2019-07-06	2019-07-01	2019-07-07	A
2019-07-01	2019-06-01	2019-07-01	B

I want to show de avg days between order and delivery for Group "A" during J-date "2019"

Have tryed with below and get an OK on the formula but it doesnt work.

=avg({<[Group]={"A"}>}{<[J-date]={"2019"}>}aggr(([Deliverydate])-([Orderdate]),[Deliverydate],[Orderdate],[Group],[J-date]))

[sergio0592 · Accepted solution]

Try with :

=avg({<[Group]={"A"},[J-date]={"2019"}>} aggr(([Deliverydate])-([Orderdate]),[Deliverydate],[Orderdate],[Group],[J-date]))

[sloman123]

Great, thanks alot!

[sergio0592]

And if you want or instead and :
want to show de avg days between order and delivery for Group "A"  or  during J-date "2019"

=avg({<[Group]={"A"}>+<[J-date]={"2019"}>} aggr(([Deliverydate])-([Orderdate]),[Deliverydate],[Orderdate],[Group],[J-date]))