How to Calculate the difference between two dates appear in 2 different tables in qlik sense

[pgloc2020]

Hello,

I have 2 different data sets -"Open_claims" and "Closed_claims" . I need to calculate the difference between the claims that are opened that week - claims that are closed that week. 

I need to display data on a weekly basis. 

I have calculated the overall difference between the open and closed claims using the following expression-

Count(Open_claim.App_ID) - Count([Closed_claim.App_ID])

But I need to create a table chart and want to display this difference on weekly basis as shown below.

Please help me how to achieve desired result. Do I have to manually select same date range from both data sets to get the difference between open and closed claims for that week?

Columns in Open_Claim table are - App_ID, Open_Date, App_Type

Columns in Closed_Claim table are - App_ID, Closed_Date, App_Type

There is no common key to join the tables.

Format of the date is MM/DD/YYYY

Thank you in advance

[vchuprina · Accepted solution]

Hi,

You can use the WeekEnd function, as an example

Table:
LOAD
     Date,
     WeekEnd(Date(Date#(Date,'MM/DD/YYYY'))) as weekend
     ;
LOAD * Inline [
Date,
4/29/2022
4/22/2022
];

Result:

With this function, you can create a weekend field in both tables and use it as a link between tables

WeekEnd(Date(Date#(Open_Date,'MM/DD/YYYY'))) as weekend

WeekEnd(Date(Date#(Closed_Date,'MM/DD/YYYY'))) as weekend

Regards,

Vitalii

[vchuprina]

Your Data model will look like this:

Regards,

Vitalii

[pgloc2020]

Thank you Vitalii,

It works as you suggested.