Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Avg days between two date columns during specific time period

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.

OrderdateDeliverydateGroup
2018010120180201A
2018010120180205B
2019010120190201A
2019010120190203A
2020010120200201B
2020010120200202A

 

Thanks in advance

 

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Try with :

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

View solution in original post

5 Replies
Highlighted
Specialist III
Specialist III

Hi,

Try with :

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

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-dateOrderdateDeliverydateGroup
2020-01-012020-01-012020-01-05A
2020-02-022020-02-012020-02-03A
2020-02-052020-01-052020-02-10A
2019-01-012019-01-012019-01-09A
2019-01-032019-01-012019-01-05A
2019-07-062019-07-012019-07-07A
2019-07-012019-06-012019-07-01B

 

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]))

 

 

Highlighted
Specialist III
Specialist III

Try with :

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

View solution in original post

Highlighted
Contributor II
Contributor II

Great, thanks alot!

Highlighted
Specialist III
Specialist III

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]))