Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I show you with an example:
I would like to sum the cost by ID and only the max year minus one:
For ID 1= 200 and for ID 2 = 1000, Total= 1200.
I tried: Sum(Aggr(ID, Max(YEARS)-1),COST)) but the result is 0.
Thank you very much!!!
Sorry @lomi89,
I didn't think I needed to add the ID.
You can add the ID as an dimension to any chart in order to get the expression per ID. Like in the image below.
If you want to pinpoint which ID to calculate then you can add an additional modifier to the set analysis to accomplish that. Like this (for ID = 1):
=SUM({<YEARS={"$(=max(YEARS,2))"}, ID={1}>}COST)
Tried from script.
Table1:
Load *, ID&'-'&YEAR as Key;
LOAD * INLINE
[
ID,YEAR,COST
1,2019,1000
1,2020,200
1,2021,3000
2,2020,1000
2,2021,5000
](delimiter is ',');
T2:
Load
ID&'-'&Max(YEAR-1) as Key,
ID&'-'&Max(YEAR-1) as Key2,
'1' as Flag
Resident Table1 Group by ID;
Hope this helps.
You can do this expression using set analysis. Try this expression:
SUM({<YEARS={"$(=max(YEARS,2))"}>}COST)
or
SUM({<YEARS={"$(=max(YEARS)-1)"}>}COST)
Thank you very much! I prefer to develop in set analysis by this case!
Sorry Vegar, in your solution you forgot the ID
Adding to @Vegar to group by ID:
SUM(Total <ID> {<YEARS={"$(=max(YEARS)-1)"}>}COST)
@lomi89 try below
=sum(aggr(if(YEAR = max(total <ID> YEAR)-1, COST),ID,YEAR))
Sorry @lomi89,
I didn't think I needed to add the ID.
You can add the ID as an dimension to any chart in order to get the expression per ID. Like in the image below.
If you want to pinpoint which ID to calculate then you can add an additional modifier to the set analysis to accomplish that. Like this (for ID = 1):
=SUM({<YEARS={"$(=max(YEARS,2))"}, ID={1}>}COST)