Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 one more option
=sum(aggr(if(Date*1e5 -sum(Value) = max(total <Type> aggr(Date*1e5 -sum(Value),Type,ID)),sum(Value)),Type,ID))
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))
@Vegar expression is not working. It says nested aggregations are not allowed.
@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
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;
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
))
@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))
@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)
Thanks for your inputs, but I don't have access to script so looking for front end solution.
@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