How to return null() when using MAX and group BY()

[#1]

Hi,

I am calculating if a product was delivered on time, sometimes there can be many deliveries of the same product, i need the latest deliver to be the time used against the expected delivery, but sometimes one of the deliveries has no date insterted yet, and it will use the max date instead of the null()

Example:

Position     expected date    Delivery date

30               04/04/2015           03/04/2015

30               04/04/2015           null()

Result after:  expected date, max(Delivery date) , Group by position;

Position     expected date    Delivery date

30               04/04/2015           03/04/2015

What i need:

Position     expected date    Delivery date

30               04/04/2015           Null()

The use of MAX is necessary since sometimes instead of null() i could have 04/04/2015, so it will display the latest delivery.

How do i get the max when is necessary, and the null() when one input is set to null()?

Regards

[swuehl]

Maybe try an expression like:

= If( NullCount([Delivery date]), NULL(), Max([Delivery date]) )

[marcus_sommer · Accepted solution]

There are several ways possible. One is to replace the NULL before you aggregated it (directly within a load per if-loop, or per NullAsValue or per Null-Mapping). Another could be to use peek/previous in a sorted load to flag the latest delivery date. Further a pick on concat() on these field is also a possibility. At first I would try something like this:

....

max(if([Delivery date] >= 1, [Delivery date], 1000000)

....

- Marcus

[#4]

Thanks, but Marcus answer worked really good!

Regards

[#5]

Perfect! Thanks