1 Reply Latest reply on May 5, 2016 2:48 PM by Boas Lee

    Getting max of date by Each Order and take date diff

    khyati.parikh

      Hi,

       

      I am trying to find number of days between two dates , Having one date as Require ship date  and comparing it with the range of date grouped by Customer PO number.

      I need to use this in creating a graph where i am not pulling in the Customer PO number

       

      using this gives me days diffeence as highlighted datediff('day',[Customer Delivery Date],[Require Shipping Date]) 

       

      How to find max of customer delivery date group by each PO and compare datediff with require ship date.

      It should be -3 through out as it should compare 2/23 with 2/26

       

      I did try to create below but didnt work.

      datediff('day',max([Customer Delivery Date]),[Require Shipping Date]) partition by Customer PO number.

       

      Created above but giving me error

      cannot mix aggregate and non aggregate arguments with this function

       

       

      can anyone help me how to approach this.

       

       

      Thanks,

      Windy

        • 1. Re: Getting max of date by Each Order and take date diff
          Boas Lee

          Hi Khyati,

           

          The message "cannot mix aggregate and nonaggregate arguments with this function" usually appears when there are fields in a calculated field that are aggregated and not aggregated. Looking at the formula, it may be possible that [required shipping date] is not aggregated. Please try to change the formula to the following. Note that I have aggregated [required shipping date].

           

          Formula

          datediff('day',max([Customer Delivery Date]),max([Require Shipping Date]))

           

          Furthermore, just because the formula is valid, does not mean it will give the calculate the expected values. If this does not resolve the issue, calculations are very dependent on how the format of the data is and what is on the view. Would it be possible to get a copy of the workbook?

           

          Regards,

          Boas