Excluding values in set expression.

[ganeshreddy]

Hi All,

I have table as shown in below,and PFA

Please help me on wring expression in UI to calculate the opportunity value, 

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

[ganeshreddy]

colin_albert‌

tresesco‌

jontydkpi‌

sureshqv‌

jagan‌

Hi All,

Can any one help me on this.

Thanks,

Ganesh

[YoussefBelloum]

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

[prat1507]

Maybe this

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

[ganeshreddy]

Hi Youssef,

Thanks for you reply,

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)).

Please help me on this.

Best Regards,

Ganesh

[ganeshreddy]

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))

Please find attached data.

Thanks,

Ganesh

[YoussefBelloum]

Find attached,

you can improve it

hope it helps !

[YoussefBelloum]

you can also add:

=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.

[prat1507 · Accepted solution]

Please find the desired app.

Regards

Pratyush

[ganeshreddy]

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