Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements

Qlik Cloud Maintenance is scheduled between March 27-30. **Visit Qlik Cloud Status page for more details.**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- How to intersect employees ID between two years an...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

fabb995

Partner - Contributor II

2022-04-29
11:19 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1 Solution

Accepted Solutions

Vegar

MVP

2022-04-30
05:57 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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])

Vegar

Qlik Community MVP

Qlik Community MVP

5 Replies

rubenmarin

MVP

2022-04-29
02:35 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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])

Vegar

MVP

2022-04-30
05:57 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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])

Vegar

Qlik Community MVP

Qlik Community MVP

fabb995

Partner - Contributor II

2022-05-02
04:33 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Fabio

fabb995

Partner - Contributor II

2022-05-02
04:33 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thank you, this is the right formula.

Fabio

rubenmarin

MVP

2022-05-02
04:40 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi, it worked for me, sample attached.

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