Count if for dynamic range

[Christian_]

I have the field “Calendarweek” in my data (numerically formatted, YYYYWW, e.g. 202437). Now I would like to calculate for each entry how often the value “Qualification” is present in the field “Diploma” within the last 8 calendar weeks. This should work independently of the currently set filters. If the value is present at least once, the formula should return “Yes”, otherwise “No”. I have tried the following formula:

If(
Count({
<Calendarweek = {"<=$(=Max(Calendarweek)) >=$(=Max(Calendarweek)-7)"},
Diploma = {'Qualification'}>
} Diploma ) > 0,
'Yes', 
'No'
)

However, this does not yet work completely. Does anyone have an idea what the problem is?

Best regards

Christian

[mpc]

Hi, 

If "This should work independently of the currently set filters" then maybe {1} for exclude all selection is missing ? 

If(
Count({1 
<Calendarweek = {"<=$(=Max(Calendarweek)) >=$(=Max(Calendarweek)-7)"},
Diploma = {'Qualification'}>
} Diploma ) > 0,
'Yes', 
'No'
)

Regards