- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sum & Aggr & Max-1 Formula
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!!!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
Qlik Community MVP
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can do this expression using set analysis. Try this expression:
SUM({<YEARS={"$(=max(YEARS,2))"}>}COST)
or
SUM({<YEARS={"$(=max(YEARS)-1)"}>}COST)
Qlik Community MVP
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much! I prefer to develop in set analysis by this case!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry Vegar, in your solution you forgot the ID
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Adding to @Vegar to group by ID:
SUM(Total <ID> {<YEARS={"$(=max(YEARS)-1)"}>}COST)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@lomi89 try below
=sum(aggr(if(YEAR = max(total <ID> YEAR)-1, COST),ID,YEAR))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
Qlik Community MVP