# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
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!

1 Solution

Accepted Solutions
Creator II

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

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\$])

Creator II

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.

Specialist

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

Contributor II
Author

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.

Contributor II
Author

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

Contributor II
Author

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

Specialist

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

Partner - Master III

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.

Creator II

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\$])

Tags
Community Browser