# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

## Filtering using values from another table, in expression

Hi all,

I currently have a main table for all my invoices, but i have 2 groups of customers, I'll name them Group A & Group B for simplicity.

I would like to do up a bar graph showing total invoice sales for these 2 group of customers.

They're all linked together by their customerID, below is a rough example of what the table structure is like(the actual tables contains thousands of customerIDs in Main invoice table, GroupA & GroupB):

Main invoice table:

customerIDInvoiceNoCustomer NameAmount\$
10011234A10
10021235B20
10031236C20
10041237D30
............

Group A table:

A.customerIDA.Customer Name
1001A
1003C
......

Group B table:

B.customerIDB.Customer Name
1002B
1004D
......

From my knowledge, I know that you can filter by sum({\$<customerID={"1001","1003"}>} [Amount\$])

But this is only practical and applicable to small tables, what I am currently working on is a table with lots of customerIDs.

Is there any way to filter by using another table's values?

Any help or comments will be greatly appreciated!

Tags (2)
1 Solution

Accepted Solutions
Contributor II

## Re: Filtering using values from another table, in expression

Probably you are looking for Alternate States in Qlik Sense

You may check this solution without using an external extension

Using Alternate State in QLikSense (without Extensions)

So you may try sum({\$<customerID=P([A.customerID])>} [Amount\$])

10 Replies
MVP

## Re: Filtering using values from another table, in expression

Assign groups to the customer in the script itself (using mapping load or if statement) and then you can just do this

Sum({\$<Group={'Group A'}>} [Amount\$])

Contributor II

## Re: Filtering using values from another table, in expression

Hi, please try P and E functions. You may get info from Stefan's post https://community.qlik.com/thread/68931

For example

sum({\$<customerID=P({1<customerID={'FieldFromOtherTable'}>} customerID)>} [Amount\$])

It can provide you dynamic selection.

Valued Contributor

## Re: Filtering using values from another table, in expression

your data model is not in shape, how you can have it Qlik. change the Customer Name as GB Customer Name and sum on that.

Regards

New Contributor II

## Re: Filtering using values from another table, in expression

Hi Max,

Thanks for your valuable reply, I've tried the formula given but it resulted in an error, but I have a feeling that the formula you gave is logically correct, and its just my application that is wrong.

Could I ask of you to kindly explain to me how it works?

I've tried sum({\$<customerID=P({1<customerID={'A.customerID'}>} customerID)>} [Amount\$])

where A.customerID is the customerID field of GroupA, which I've associated in the data model manager as such.

New Contributor II

## Re: Filtering using values from another table, in expression

Hi Hashmi,

What I've given are the base table fields before association, sorry for not being clear with my question. I've edited my question for better clarification.

Best Regards,

Yi Xuan

New Contributor II

## Re: Filtering using values from another table, in expression

Hi Sunny,

Is this applicable for tables with regularly changing data? I might just use this if nothing else works

Thanks alot!

Best Regards,

Yi Xuan

Valued Contributor

## Re: Filtering using values from another table, in expression

Sum({\$<Group={'Group A'}>} [Amount\$])

Honored Contributor II

## Re: Filtering using values from another table, in expression

I suggest you to create a new field in the Customer table with the customer's group he belongs to (A/B).

Finally in your bar chart you can use it as dimension value.

Contributor II

## Re: Filtering using values from another table, in expression

Probably you are looking for Alternate States in Qlik Sense

You may check this solution without using an external extension

Using Alternate State in QLikSense (without Extensions)

So you may try sum({\$<customerID=P([A.customerID])>} [Amount\$])