5 Replies Latest reply on Jan 31, 2019 10:14 PM by Zhouyi Zhang

    Min Date caculation

    Michael Dawson

      I have a report were I look at how long a process take,s the report shown looks at when the order closes and what is the average time is but I would like to look at the average time from the first invoice but I'm having problems please see screenshots.


      sometime there are more than one invoices so I would like to look at the first Min(invoice date) but when I put this into the calculation field I get the mix aggregate error.

        • 1. Re: Min Date caculation
          Zhouyi Zhang

          Hi, Michael


          withoug seeing a sample workbook, it is hard to give a solution, so if you can provide one, it will be easier to look at it.

          Meanwhile, try to fix your syntax issue, try use {Fixed [Invoice number]:min([Invoice Date])} instead of your min([invoice date]) in your calculation.


          Hope this helps



          • 2. Re: Min Date caculation
            Michael Dawson

            Hi Zhouyi,


            would this work if I have three invoices two with the same invoice date and one with a different date I have attached a screenshot.



            Michael.30-01-2019 16-05-59.png

            • 3. Re: Min Date caculation
              Zhouyi Zhang

              Hi, Michael


              If I understand correctly, you are trying to get the min date for each invoice number, so in your sample screenshot, the invoice # are different, so it just gets the min date.



              • 4. Re: Min Date caculation
                Michael Dawson

                Hi ZZ


                What Im trying to do is look at the first invoice date and then calculate how many days there are between the start date(order date) to the end day(Invoice date) but only the business day so excluding the weekends but with the example above it is giving me 27.46 days when it should be 33 start date 31/07/2018 - 14/09/2018 take away the weekends = 33.


                I have two calculation fields first one


                DATEDIFF('week',[Order Date],{Fixed [Invoice Number]:min([Invoice Date.])})*5




                MIN(DATEPART('weekday',[Order Date]),6)




                MIN(DATEPART('weekday',{Fixed [Invoice Number]:min([Invoice Date.])}),6)


                and then below to get the average


                SUM([Days to Complete Order])/SUM([Number of Records])



                • 5. Re: Min Date caculation
                  Zhouyi Zhang

                  Hi, Michael


                  This won't be that easy as you want calculate the business days as well. you need some sort of data structure redesign to achieve it, and you also need a calendar table which identifies the business days with a flag.