Announcements
cancel
Showing results for
Did you mean:
Creator III

## Excluding values in set expression.

Hi All,

I have table as shown in below,and PFA

Exclude the 'Customer' value matched rows to 'opp string' string to get total GWP_GBP opportunity value.

i.e. Opportunity = sum(Exclude the rows from the calculation where 'opp string' includes 'customer' value. So, if anywhere in the pipe separated listed and a single 'customer' appears, the row will be excluded for total oppotunity)

Thanks,

Ganesh

1 Solution

Accepted Solutions
Specialist

Regards

Pratyush

11 Replies
Creator III
Author

Hi All,

Can any one help me on this.

Thanks,

Ganesh

Champion

Hi,

can you be more clear ?

Exclude the 'Customer' value matched rows to 'opp string' string ....

sum(Exclude the rows from the calculation where 'opp string' includes 'customer'

maybe i don't understand you, but i don't see Customer value

Specialist

Maybe this

=Sum(Aggr(If(SubStringCount([opp string],customer)=0,Sum(Sales),0),customer,[opp string]))

Creator III
Author

Hi Youssef,

Yes your understanding is correct, sum(<Exclude the rows from the calculation where 'opp string' includes 'customer'>Sales)

In UI, I have a straight table with customer as a dimension, the above mentioned has to be an expression.

FYI, In the given table first column has customer values.

For example 'tryu' is present in 3 rows i.e row no 1, 7 and 11 respectively so exclude those rows , for 'tryu ' the opportunity value, the value has to be 425 (i.e. 590-(50+85+30)).

Best Regards,

Ganesh

Creator III
Author

Hi Pratyush,

The provided expression is not working, only getting two rows in output and those values are not correct.

However the expected result for 'kitent' is 360 i.e(590-(30+40+80+60+20))

Thanks,

Ganesh

Champion

Find attached,

you can improve it

hope it helps !

Champion

=if(GetSelectedCount(customer)<>0,

if(only( {<customer=>} wildmatch(replace([opp string],'|',''),'*'&\$(x)&'*'))<>1,

sum({<customer=>}sales),'NOK'), sum(sales))

just to always have the total if no customer is selected.

Specialist

Regards

Pratyush

Creator III
Author

Hi Youssef,

Thanks for your efforts on this, unfortunately our requirement is no to select any customer name, the default out put has to be like tihs.

How to achieve the required output.

Thanks,

Ganesh

Community Browser