How to return the latest (max) date for a parent field

[dawgfather]

So I have a series of data with parent/child relationships and all of the child items can have different dates assigned.

I am looking to find out how to create an expression so that the parent item/field will show the latest (max) date assigned to one of its children.

Example:

Product      Model        Date

Widgets      Widget1      1/7/2018

Widgets      Widget2      2/1/2018

Widgets      Widget3      3/1/2018       (Latest date)

Widgets      Widget4      2/28/2018

Blocks         Block1        1/15/2018

Blocks         Block2        1/18/2018

Blocks         Block3        1/31/2017

Blocks         Block4        2/15/2018     (Latest date)

Expected/desired return:

Widgets    3/1/2018

Blocks      2/15/2018

I tried MAX and a few other things, but I know there's got to be something relatively easy that I am missing.

[sunny_talwar · Accepted solution]

Create a chart like this

Dimension

Product

Expression

Date(Max(Date))

[mdmukramali]

Hi,

Are you looking the solution in the front-end or in the back-end?

Front-end:

Use the Dimensions:

Product

=Date(Aggr(Max(Date),Product),'DD/MM/YYYY')

Expression : 1

Hide the expression

if you are looking in the back-end,

then

Test:

LOAD * Inline

[

Product,Model,  Date

Widgets,Widget1,1/7/2018

Widgets,Widget2,2/1/2018

Widgets,Widget3,3/1/2018

Widgets,Widget4,2/28/2018

Blocks,Block1,1/15/2018

Blocks,Block2,1/18/2018

Blocks,Block3,1/31/2017

Blocks,Block4,2/15/2018

];

Left Join (Test)

LOAD

Product,

Date(Max(Date),'DD/MM/YYYY') AS MaxDate

Resident Test

Group by Product;

[vishsaggi]

Also you can this in your expression:

Dim: Product

Expr: = FirstSortedValue(DateField, -Aggr(DateField, Product, DateField))