KPI average of difference in dates, but limited to date range (30 days) - stuck!

[Daryn]

Hi helpful folks.

I have a working expression that give me an average of days taken to ship, based on the difference between the fields. Created and Shipped. 

AVG(Ceil(Interval(Date#([Shipped], 'DD.MM.YYYY') - Date#([Created], 'DD.MM.YYYY'), 'D' )))

I wanted to limit the range of this to 30 days. But have got in a right muddle, I'm embarrassed to post the below really, but have obviously got my parenthesis and such very confused. I assumed I only needed to add the 30 day restriction to one side of the equation/expression as well?

avg (Ceil (Interval ( Date# ([Ac.GI date] >= today () -30, 'DD.MM.YYYY')), - Date# ([Created on], 'DD.MM.YYYY'), 'D' ))

I apologize in advance for my useless attempt! 

Thanks for taking the time to read this and help if you are able.

Regards Daryn

*100 views* but no suggestions/pointers.  Anyone please....?

[SBDataspark · Accepted solution]

Hi there Daryn,

Just came across your question.
Will adding a set analysis to your avg expression do the trick?

AVG({<[Shipped]={">=$(=Date(Today()-30,'DD.MM.YYY')))"} Ceil(Interval(Date#([Shipped], 'DD.MM.YYYY') - Date#([Created], 'DD.MM.YYYY'), 'D' )))

Kind regards,

Sebb

[Daryn · Accepted solution]

Hi Sebb,

Thanks for the reply. I will have a look at this later/tomorrow. Really appreciate it. 

Regards Daryn

[Daryn]

Hi Sebb,

Worked a treat 👍

Regards, Daryn.

[SBDataspark]

Hi Daryn,

Good to hear!
You're welcome...😊

Kind regards,

Sebb