Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
npatel1
Contributor II
Contributor II

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

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:
YearPeriodDocument codeClearing DocumentNumberAR valueInvoice numbercheque numberpayment term
202412RV35353510012345 N30
202412AB353535-100   
20251DZ353535100 55557 
20252RV98999850067899 N60
20253DZ989998500 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
YearPeriodDocument codeClearing DocumentNumberAR valueInvoice number
202412RV35353510012345
202412AB353535-100 
 
 
PAYMENTS
YearPeriodDocument codeClearing DocumentNumberAR valuecheque number
20251DZ35353510055557
 
 
 
Appreciate your help.
 
 
1 Reply
rubenmarin1

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