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

Wed, 25 Nov 2020 16:16:04 GMT

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?

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

swuehl — Thu, 26 May 2016 22:00:15 GMT

Try as expression in a text box

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

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

Thu, 26 May 2016 22:36:12 GMT

That's exactly what I needed. Thank you!

topic Re: Determine if a list of available values in field1 is in field2. in QlikView

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

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

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