Filtering using values from another table, in expression

[Anonymous]

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:

customerID	InvoiceNo	Customer Name	Amount$
1001	1234	A	10
1002	1235	B	20
1003	1236	C	20
1004	1237	D	30
...	...	...	...

Group A table:

A.customerID	A.Customer Name
1001	A
1003	C
...	...

Group B table:

B.customerID	B.Customer Name
1002	B
1004	D
...	...

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!

[sunny_talwar]

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

[maxsheva]

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.

[zebhashmi]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[arvind1494]

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

try above in your exprerssion.

[agigliotti]

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.

[maxsheva · Accepted solution]

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