Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lomi89
Contributor III
Contributor III

Sum & Aggr & Max-1 Formula

Hi Team,

I show you with an example:

lomi89_0-1629443226103.png

 


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!!!

 

Labels (2)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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.

Vegar_0-1629457867301.png

 

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)

 

 

View solution in original post

8 Replies
AshutoshBhumkar
Partner - Specialist
Partner - Specialist

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;

 

AshutoshBhumkar_0-1629445120916.png

Hope this helps.

Vegar
MVP
MVP

You can do this expression using set analysis. Try this expression: 

SUM({<YEARS={"$(=max(YEARS,2))"}>}COST)

or

SUM({<YEARS={"$(=max(YEARS)-1)"}>}COST)

lomi89
Contributor III
Contributor III
Author

Thank you very much! I prefer to develop in set analysis by this case!

lomi89
Contributor III
Contributor III
Author

Sorry Vegar, in your solution you forgot the ID

agigliotti
Partner - Champion
Partner - Champion

Hi @lomi89 

You should use what @Vegar  suggested above as measure and ID as dimension.

Best Regards

stevejoyce
Specialist II
Specialist II

Adding to @Vegar  to group by ID:

SUM(Total <ID> {<YEARS={"$(=max(YEARS)-1)"}>}COST)

Kushal_Chawda

@lomi89  try below

=sum(aggr(if(YEAR = max(total <ID> YEAR)-1, COST),ID,YEAR))

Vegar
MVP
MVP

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.

Vegar_0-1629457867301.png

 

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)