Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Determine if a list of available values in field1 is in field2.

Hello,

I am trying to create a calculation that sums all the corresponding records from one table based on the matching values between the summed table and another table.

For example, in the attached test file I have a SalesTeam and ProductTeam table. SalesTeam and ProductTeam have overlapping team name values but the columns are intentionally named differently (this is required for my data model for security reasons). If the available SalesTeam team name values exist in the available ProductTeam team name values, I want to show a sum of the sales amount.

i.e. If no Product Teams are selected, the total should show 500 + 1000 + 400 = 1900

     If Team1 is selected, the total should show 500

     If Team1 and Team2 is selected, the total should show 500 + 1000 = 1500

I am attempting to do this with an If statement like:

     If(Match(SalesTeam, Concat(ProductTeam, ',')), Sum(Amount))

as well as:

     If(SalesTeam=ProductTeam, Sum(Amount))

However, you'll notice that these only work when there are single values selected for ProductTeam and SalesTeam.

How can I make these expressions work if there are one or more values selected in ProductTeam and none selected for SalesTeam?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try as expression in a text box

=Sum({<SalesTeam = p(ProductTeam)>} Amount)

View solution in original post

2 Replies
swuehl
MVP
MVP

Try as expression in a text box

=Sum({<SalesTeam = p(ProductTeam)>} Amount)

Not applicable
Author

That's exactly what I needed. Thank you!