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

# Min Date caculation

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

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.

Hope this helps

ZZ

• ###### 2. Re: Min Date caculation

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.

Thanks,

Michael.

• ###### 3. Re: Min Date caculation

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.

ZZ

• ###### 4. Re: Min Date caculation

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])

Michael.

• ###### 5. Re: Min Date caculation

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.

ZZ