Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Create a chart like this
Dimension
Product
Expression
Date(Max(Date))
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;
Also you can this in your expression:
Dim: Product
Expr: = FirstSortedValue(DateField, -Aggr(DateField, Product, DateField))