3 Replies Latest reply: Aug 23, 2017 5:32 AM by Florian Saner RSS

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

    Florian Saner

      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

        • Re: Does a Max() work within an AVG()?
          lakshmipathi p

          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,

          • Re: Does a Max() work within an AVG()?
            Andrea Gigliotti

            maybe this:

             

            =avg( aggr(

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

            0,

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

              • Re: Does a Max() work within an AVG()?
                Florian Saner

                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