Qlik Sense - How to filter data based on 1 column, but result should display data based on the link of another column

[npatel1]

I have a specific requirement for Accounts receivables.

I would prefer to not make changes in the load script and would prefer front end expressions.

This is an example of my source table:

Year	Period	Document code	Clearing DocumentNumber	AR value	Invoice number	cheque number	payment term
2024	12	RV	353535	100	12345		N30
2024	12	AB	353535	-100
2025	1	DZ	353535	100		55557
2025	2	RV	989998	500	67899		N60
2025	3	DZ	989998	500		88995

- The document type RV are invoices, AB is system generated closing document for the invoice and DZ are the payments received.

- Clearing Document Number shows which cheque was used to pay which invoice.(ClearingDocumentNumber is our linking column)

Requirement:

If I select an invoice number or a payment term on Qlik Sense sheet, the respective cheque should also display in the graph and table.

e.g. If user filter by selecting N30 on Qlik Sense, OR If user selects cheque number 55557 or invoice number 12345.

below 2 tables should show data as:

INVOICES
Year	Period	Document code	Clearing DocumentNumber	AR value	Invoice number
2024	12	RV	353535	100	12345
2024	12	AB	353535	-100

PAYMENTS
Year	Period	Document code	Clearing DocumentNumber	AR value	cheque number
2025	1	DZ	353535	100	55557

Appreciate your help.

[rubenmarin · Accepted solution]

Hi, you can use tables with all fields as dimensions except AR Value, which can be a measure like:

Invoice table: Sum({<[Document code]={'RV','AB'}, [Clearing DocumentNumber]=P([Clearing DocumentNumber]),[payment term],[Invoice number],[cheque number]>} [AR value])

Payments table: Sum({<[Document code]={'RV','AB'}, [Clearing DocumentNumber]=P([Clearing DocumentNumber]),[payment term],[Invoice number],[cheque number]>} [AR value])

You can also use [Document code]*={'RV','AB'} if you want to filter Document code values