Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Set analysis, comparing dates with P()

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:

Key,

OrderDate,
OrderNumber,
OrderNumberRowId,
ProductNumber

Invoices:

Key,

InvoiceNumber,

InvoiceNumberRowId,

InvoiceDate

Products:

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?

Cheers

Anders

1 Solution

Accepted Solutions
Not applicable
Author

Excellent Henric! Problem solved, thanks for the advice!

/Anders

2 Replies
Former Employee

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

Not applicable
Author

Excellent Henric! Problem solved, thanks for the advice!

/Anders

Community Browser