Does a Max() work within an AVG()?

[#1]

Hi everyone,

To sum up, I have a table with Order Number, delivery date and delivery time.

Since an order can have more than one date of delivery I use a max function to consider only the longest delivery time:

if((OrderDate>DeliveryDate or (NetWorkDays ([OrderDate],DeliveryDate)-1)<0),

0,Max(NetWorkDays ([OrderDate],DeliveryDate)-1))

This function work correctly and I would like to get the AVG() of all these delivery time within a KPI.

I've tried a few function such as the following one but w/o success:

if((OrderDate>DeliveryDate or (NetWorkDays ([OrderDate],DeliveryDate)-1)<0),

0,avg(Max(NetWorkDays ([Date_Commande_SCO],DeliveryDate)-1)))

Thank you in advance for your help

Florian

[pathiqvd · Accepted solution]

Hi,

   Try like this,

avg(if((OrderDate>DeliveryDate or (NetWorkDays ([OrderDate],DeliveryDate)-1)<0),

0, aggr(Max(NetWorkDays ([Date_Commande_SCO],DeliveryDate)-1),[Date_Commande_SCO])) )

Regards,

[agigliotti]

maybe this:

=avg( aggr(

if( OrderDate>DeliveryDate or (NetWorkDays ([OrderDate],DeliveryDate)-1)<0 ),

0,

Max( NetWorkDays([Date_Commande_SCO],DeliveryDate)-1 ), [Order Number] ) )

[#4]

It's working with you solution Iakshmipathi! Thanks to both of you for your help!

Just to know I had to change the dimension of my table because some data where missing and now to my table take into account all the delivery time for one order and not only the longest one such as:

Order: 01.01.2017

Date of delivery1: 03.01.2017, delivery time 2 days

Date of delivery2: 04.01.2017, delivery time 3 days

I would like to get only 3 days as a result of my function:

if((OrderDate_SCO>DeliveryDate_SLC or (NetWorkDays ([OrderDate_SCO],DeliveryDate_SLC)-1)<0),

0,Max(NetWorkDays ([OrderDate_SCO],DeliveryDate_SLC)-1))

Do you have any idea what's wrong with this function?

Regards,

Florian