Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Set analysis in qv is very powerful. Try implicit intersection like:
Sum({<DEPT * = {'DeptA', 'DeptB'}>} NoStaff)
Set analysis in qv is very powerful. Try implicit intersection like:
Sum({<DEPT * = {'DeptA', 'DeptB'}>} NoStaff)
Try
if(wildmatch(getfieldselection(DEPT),'DeptA','DeptB'),sum({<DEPT={'DeptA', 'DeptB'}>}NoStaff),0)
Thanks!
Thank you!