Issue with conditional summing

[amr_qlik]

Hi,

I'm using the data below and I need to be able to calculate  the net change in the number of employees  working at a company  at the starting year compared to the ending year.   The years listed could be anything, not just 2020 and 2021.  Based on the data below, the answer for company one would be 0 and the answer for company 2 would be -5.  

I found a similar solution here: https://community.qlik.com/t5/App-Development/How-to-get-a-conditional-sum/td-p/1075627 

and am trying to modify it to my scenario, but so far can't seem to get it to work.  My equation is as follows:

sum({$ <Year = MAX(Year)> } # of Employees (Client Level))  -  sum({$ <Year = MIN(Year)> } # of Employees (Client Level))

Data:

LOAD * INLINE [

Client, Company, # of Employees (Client Level), Year

1, 1, 10, 2020

2, 1, 10, 2021

3, 2, 5, 2020

4, 2, 5, 2020

5, 2, 5, 2021

6, 2, 5, 2021

7, 2, 5, 2020

];

Any help is appreciated.  Thank you!

[Digvijay_Singh · Accepted solution]

Try this modified expression - 

sum({$ <Year = {$(=MAX(Year))}>} [# of Employees (Client Level)]) - sum({$<Year = {$(=MIN(Year))}>} [# of Employees (Client Level)])