# App Development

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
Partner - Contributor II

## How to intersect employees ID between two years and obtain the amount of cost in the current year

Hi all,

I have a dataset that contains the list of employees ID and their cost.

I want to create a calculated measure that intersect employees ID that exist in 2021 and 2022

 2021 Employee ID Cost 1 10 2 15 4 20

 2022 Employee ID Cost 1 20 2 30 5 40

and extract their total cost ONLY in 2022 (equal to the sum of  the cost of Employee ID 1 and 2, 20+30=50).

Using this set analysis expression,

SUM({<[Employee ID] = P({<YEAR={2022}>})*P({<YEAR={2021}>})>}[Cost])

I can extract the total cost of employees ID 1 and 2 (75) but not the cost related to 2022 for employees ID 1 and 2 (50 would be the right value).

Using the next set analysis expression I can't extract the right value:

SUM({<YEAR={2022}>}{<[Employee ID] = P({<YEAR={2022}>})*P({<YEAR={2021}>})>}[Cost])

How I can correct the set analysis expression? It exists another way to reach my goal?

Thank you in advance for the support,

Fabio.

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
MVP

I'm not sure you need the P({<YEAR={2022}>}). You will get that reduction from your YEAR={2022}. I think you should be able to simplify it to this expression:

SUM({<YEAR={2022},[Employee ID] = P({<YEAR={2021}>})>}[Cost])

5 Replies
MVP

Hi, you were close, the syntax to use two filters is separating fields by comma:

SUM({<YEAR={2022},[Employee ID] = P({<YEAR={2022}>})*P({<YEAR={2021}>})>}[Cost])

MVP

I'm not sure you need the P({<YEAR={2022}>}). You will get that reduction from your YEAR={2022}. I think you should be able to simplify it to this expression:

SUM({<YEAR={2022},[Employee ID] = P({<YEAR={2021}>})>}[Cost])

Partner - Contributor II
Author

Thanks for the answer, but this formula gives me a null value.

Fabio

Partner - Contributor II
Author

Thank you, this is the right formula.

Fabio

MVP

Hi, it worked for me, sample attached.

Anyway, Vegar answer is better, I just focused on the syntax.

Tags
Community Browser