Announcements
cancel
Showing results for
Did you mean:
Creator III

## Logic help

Hi All,

I have table as attached, how to get the 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).

Thanks,

Ganesh

8 Replies
Champion III

May be try this?

Tab:

LOAD *, IF( Index(String, 'CUSTOMER') > 0, 1, 0) AS CustFlag;

upper([opp string]) as String,

Sales

FROM

[New Microsoft Excel Workbook.xlsx]

(ooxml, embedded labels, table is Sheet1);

I added couple of values as customer inside your opp string fields you can add and check.

Dim: customer, String

Expr: = Sum({< CustFlag = {0} >} Sales)

Creator III
Author

Here we can not hard code any customer names in logic as we have so many customers present in table and the calculation should apply to every row of the table, However the comparison has to happen in between the strings (customer, opp string) in the logic.

Thanks,

Ganesh

Creator III
Author

Hi tresesco

Can you help me on this.

Thanks,

Ganesh

MVP

This?

Sum({<[opp string] -={"*customer*"}>}Sales)

Partner - Champion III

Create a table with customer as dimension and this as the expression:

Sum({<customer = {"\$(=index('|' & [opp string] & '|', '|' & customer & '|') = 0)"}>} Sales)

or

Sum(If(index('|' & [opp string] & '|', '|' & customer & '|') = 0, Sales))

The pipes '|' avoid problems if one customer name is also part of another customer name (eg 'gia' and 'gia1' would be distinct)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Master II

Hi Ganesh,

Can you please explain your requirement a bit  more clearly ? What do you mean by customer name ?

Or you share a expected output from the file you have provided.

Br,

KC

Best Regards,
KC
Master II

Jonathan's solution should work for you

Best Regards,
KC
Champion III

I did not meant to hardcode any values your excel sheet doesnot have any text customer so for testing purposes i added.

Community Browser