Len() and WildMatch() in set analysis

[Cascader]

hi

I have the following expression, 

len(trim(if(WildMatch(Queue ,'*'&Dept&'*'), Dept)))>0

I want to use inside a the following set analysis

aggr(ONLY({<Is_Dept_Group={'1'},Is_Case_Owner={'1'},_Closed={'0'}>}[Case Number]),[Case Number])

[rwunderlich]

Add the set modifier:

[Case Number]={"=len(trim(if(WildMatch(Queue ,'*'&Dept&'*'), Dept)))>0"}

-Rob

[marcus_sommer]

I think it's not possible within a classical set analysis because your condition requires a row-level evaluation. I think I would adjust a bit and using it as a boolean-condition within the aggr(), like:

aggr(
ONLY({<Is_Dept_Group={'1'},
                Is_Case_Owner={'1'},
                _Closed={'0'}>} [Case Number]) * sign(WildMatch(Queue ,'*'&Dept&'*'))
,[Case Number])

- Marcus

[Cascader]

not worked, Rob!

[Cascader]

not working either

[Cascader · Accepted solution]

I found this work around, not sure if it's best practice or not, however it give me the correct result

I used if to check the Lenght first and if it's > 0, then do the aggregation. 

if(len(trim(if(WildMatch(Queue ,'*'&Dept&'*'), Dept)))>0, aggr(ONLY({<Is_Dept_Group={'1'},Is_Case_Owner={'1'},_Closed={'0'}>}[Case Number]),[Case Number]))