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.

[Vegar]

You will probably need to do a nested aggregation of some kind. 

Use Type as a dimension in your table and i think you could try something like the expression below as a measure. 

Only(if(Date=max(Date), aggr(Min(Value),Date))

[Santosh_K]

@Vegar  expression is not working. It says nested aggregations are not allowed.

[Santosh_K]

@Vegar  Also, If there are two records  with minimum value. It should add those two records. Please see the below scenario

data:
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
A,5,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 ];

Output:

Type  Value

A           240

B            20

[Saravanan_Desingh]

It will be easy in the Script.

SET DateFormat='DD/MM/YYYY';

tab1:
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
A,5,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
];

Left Join(tab1)
LOAD Type, Date(Max(Date)) As Max_Date
Resident tab1
Group By Type;

Left Join(tab1)
LOAD Type, Date, Min(Value) As Min_Value
Resident tab1
Where Date=Max_Date
Group By Type, Date;

Left Join(tab1)
LOAD Type, Date, Value, Sum(Value) As Sum_Value
Resident tab1
Where Date=Max_Date And Value=Min_Value
Group By Type, Date, Value;

[Vegar]

I used your data set and got this expression to generate your desired output.

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

[Kushal_Chawda · Accepted solution]

@Santosh_K  one more option

=sum(aggr(if(Date*1e5 -sum(Value) = max(total <Type>  aggr(Date*1e5 -sum(Value),Type,ID)),sum(Value)),Type,ID))

[Kushal_Chawda]

@Santosh_K  If you want more performant way to do it, I would suggest to create mix approach with script and expression

Data:
LOAD Type,
     ID,
     Date,
     Value,
     Date*100000 - Value as Number
FROM Table;

left join(Data)
LOAD Type,
     max(Number) as Number
     1 as Flag
resident Data
group by Type;

drop field Number;

Now you can simply use below expression in graph

=sum({<Flag={1}>}Value)

[Santosh_K]

Thanks for your inputs, but I don't have access to script so looking for front end solution.

[Santosh_K]

@Vegar  it's not working in all scenarios. for eg. below

A,4,16/03/2021,120
A,5,16/03/2021,120
A,6,17/03/2021,120

Here, output should be just 120 because on latest date only one transaction, but it's giving 360 with your expression