Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

If filter selection by user exists in list, then use a certain expression, else 0

Hi, I have a filter on this field DEPT that user can select from a list of values (e.g. DeptA, DeptB, DeptC, DeptD)

In a measure, i want to set up an expression that calculates NoStaff which depends on what DEPT is selected by the user.

If user select DeptA, the measure returns value using NoStaff in DeptA

If user select DeptB, the measure returns value using NoStaff in DeptB

if user select DeptC &/or DeptD, the measure returns '0'

If user select DeptA & DeptC, the measure returns value using NoStaff in DeptA only

If user select DeptA, DeptB & DeptC, the measure returns value using NoStaff in DeptA & DeptB only

...

Based on the above logic, how should I set up my expression?

I know I can use If, then, else. But this seems to suggest that I need to list every possible permutation of choice by the user. Is there an easier way, similar to the way SQL allows

if (DEPT in ('DeptA', 'DeptB'),  sum({<DEPT={'DeptA', 'DeptB'}>}NoStaff),0)--> but I'm not sure if this will handle case where user selects DeptA and DeptC.

Any advice?

1 Solution

Accepted Solutions
MVP
MVP

Set analysis in qv is very powerful. Try implicit intersection  like:

Sum({<DEPT * = {'DeptA', 'DeptB'}>} NoStaff)

View solution in original post

4 Replies
MVP
MVP

Set analysis in qv is very powerful. Try implicit intersection  like:

Sum({<DEPT * = {'DeptA', 'DeptB'}>} NoStaff)

View solution in original post

Partner
Partner

Try

if(wildmatch(getfieldselection(DEPT),'DeptA','DeptB'),sum({<DEPT={'DeptA', 'DeptB'}>}NoStaff),0)

Contributor III
Contributor III

Thanks!

Contributor III
Contributor III

Thank you!