Help to write expression

[Santosh_K]

Hi All,

I have below datset

Load * Inline [
Type,ID, Date, Value
A,1,11/01/2021,100
A,2,15/02/2021,50
A,3,16/03/2021,150
A,4,16/03/2021,120
B,1,14/01/2021,70
B,2,25/01/2021,90
B,3,18/02/2021,110
B,4,18/02/2021,20 ];

I want to create below report

Type     Value

A             120

B             20

For eg. In Type A, maximum Date is 16/03/201, for which minimum value is 120, so pick that value. In type B, maximum date is 18/02/2021, for which minimum value is 20, so pick that value.

How do I write the expression to achieve this report.

[Santosh_K]

@Kushal_Chawda  it's working as expected. Thak you so much..

[Santosh_K]

@Kushal_Chawda  This script solution looks good but I don't have access to scripts.

[Vegar]

It could be my totaling that is wrong. I removed the TOTAL <Type, Date>  from my sum(Value) expression.

=sum(
 aggr(
   IF( Date= max(total <Type> Date),
     IF(Value=min(total <Type,Date> Value), sum(Value))),
   Type,Date,Value
 ))