Set analysis, comparing dates with P()

[#1]

Hi,

A little help with a set analysis would be much appreciated. I have three tables. One table containing customer orders, one containing customer invoices and finally a table with product information. A bit simplified, they look like the example below.

Orders:
LOAD

Key,

OrderDate,
OrderNumber,
OrderNumberRowId,
ProductNumber

Invoices:
LOAD

Key,

InvoiceNumber,

InvoiceNumberRowId,

InvoiceDate

Products:
LOAD

ProductNumber,
ArticleName,
ReleaseDate

In order to monitor how fast an order is processed, I want a chart that counts the number of working days from OrderDate to InvoiceDate. With a lot of trial and error I came up with the expression below that seems to do that good.

Avg(aggr(NetWorkDays(Date(OrderDate), Date(InvoiceDate)), OrderNumberRowId)) 

The problem is I also want to exclude products where the ReleaseDate is greater than the OrderDate. Articles with a ReleaseDate cannot be processed until that date has passed. I have tried to do this using the P() function and E() function but without success.

Avg({$<OrderNumberRowId =P({$<ReleaseDate ={"<=$(=Max(OrderDate))"}>} OrderNumberRowId)>} aggr(NetWorkDays(Date(OrderDate),Date(InvoiceDate)), OrderNumberRowId)) 

Any advice? I thinking I could solve this by doing some changes to the data model. Adding another field to the Orders table that compares releaseDate with the OrderDate, but should it not be possible to solve with some function in a set analysis as well?

Thanks in advance.

Cheers

Anders

[hic]

Set analysis is not evaluated on a row-by-row basis, so you cannot use set analysis here.

Further, you need an aggregation function inside the Aggr(), which you don't have now. You should probably use something like

Avg(Aggr(

     Avg(If(

          OrderDate>ReleaseDate,

          NetWorkDays(OrderDate, InvoiceDate)

          )),

     OrderNumberRowId

     ))

HIC

[#3 · Accepted solution]

Excellent Henric! Problem solved, thanks for the advice! 

/Anders