Count dimension min(date)

[Lei]

Hi,

I have 2 tables: Vacancies and Publications. The following two tables are examples as how the tables look like:

VacancyNo	Job Title
1	HW Designer
2	Engineer
3	SW Designer
4	System Architect
5	Accountmanager

PublicationID	PublicationStart	VacancyNo
1	01-04-2020	1
2	03-08-2020	2
3	07-12-2020	3
4	05-04-2021	4
5	06-03-2022	1
6	21-04-2022	5
7	30-05-2022	6
8	02-07-2022	3

As you can see in this example, VacancyNo 1 and VacancyNo 3 both have two Publications with a different date as PublicationStart.

My goal is to count the vacancies with a publication in 2022, with exception if the vacancy has a Publication in an earlier year.

I've tried multiple expressions, but none has the desirable effect. Here are some expressions I've tried:

count({<PublicationStart.autoCalendar.Year = {'2022'}>} VacancyNo)
This gives as result all vacancies with a publication in 2022, including VacancyNo 1 and 3.

Count( Min({<[PublicationStart.autoCalendar.Year]) = {'2022'}>} VacancyNo)
Error in set modifier expression

=If(Min(Year(PublicationStart) = '2022'), count(VacancyNo))
Return the count() of all vacancies, regardless of which year PublicationStart was in

I hope someone's got the right solution for me

[rubenmarin · Accepted solution]

Hi, maybe there is something easier but this can work:

count(DISTINCT {<VacancyNo=P({<PublicationStart.autoCalendar.Year={2022}>})*E({<PublicationStart.autoCalendar.Year={"<2022"}>})>} VacancyNo)

[Lei]

Hi @rubenmarin 

Thank you for your answer. This was the solution I was looking for!