Data from two tables

[jduluc12]

Hi,

I got two tables.

Item:

Item	date	Qty
Item-1	03/06/2022	1
Item-2	04/09/2022	2
Item-3	05/10/2022	3

Sales:

Item	Sales Date
Item-1	03/08/2022
Item-2	04/10/2022
Item-3	05/12/2022

If the date in the report is set to 04/10/22

then the table should show

Item	Date	Qty
Item-1	03/08/2022	1
Item-2	04/10/2022	2

If the date in the report is set to 04/08/22

then the table should show

Item	Date	Qty
Item-1	03/08/2022	1

If the date in the report is set to 03/07/22

then the table should show no data.

Item

Date

Qty

So the expression should look at the Sales table and find if there is any row for a particular item with a sales date less or equal to the date, then it should show the data. 

Thanks

[Michiel_QV_Fan]

First:

left join Sales Item on Item.

Second:

Create a canonical date: https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578

Third:

create a table with this expression:

Sum({<[Sales date] =  {"<=$(=Date)"}>}Qty)