Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Christian_
Contributor II
Contributor II

Count if for dynamic range

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

Labels (2)
1 Reply
mpc
Partner Ambassador
Partner Ambassador

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

From Next Decision and mpc with love