Canonical Date associations problem

orlando162 — Wed, 28 Jul 2021 14:58:11 GMT

I tried to create a canonical date to join [Expected Signature Date]-[Date of Signature]-[Timeline]-[Date of issue].

I would like to list all the invoices expected/issued/planned in a specific YearMonth. It works partially, because filtering a table data with (i.e.) [Canonical Date] = 'jan-2021' Qlik shows me all the invoices associated to the [Project] which has a single expected/issued/planned invoice in 'jan-2021' (including the ones in feb-2021, mar-2021, ..., jul 2022).

For example ---> [Canonical Date] = 'jan-2021'

Project ABC jan-2021 invoice 01/2021

Project ABC mar-2021 invoice 03/2021

Project ABC jul-2022 invoice 45/2021

ProjectDEF dec-2020 invoice 78/2020

ProjectDEF jan-2021 invoice 02/2021

ProjectDEF jun-2021 invoice 38/2020

I would like to list PROJECT ABC - invoice 01/2021 and PROJECT DEF - invoice 02/2021 only. How to do that?

Attached below my current script.

DateBridge:
Load Distinct
'Project Exp Date' as Master_Project
,[Project]
,[Expected Signature Date] as Canonical_Date
Resident [Offer & Agreement];

Load Distinct
'Project Sign Date' as Master_Project
,[Project]
,[Date of Signature] as Canonical_Date
Resident [Offer & Agreement];

Load Distinct
'Project Invoice Plan' as Master_Project
,[Project]
,[Timeline] as Canonical_Date
Resident [Invoice Plan];

Load Distinct
'Project Invoiced' as Master_Project
,[Project]
,[Date of issue] as Canonical_Date
Resident [Invoice Plan];

MasterCalendar:
Load
Date(TempDate) as Canonical_Date
,Year(TempDate) as Master_Year
,'Q' & Ceil(Month(TempDate)/3) as Master_Quarter
,Month(TempDate) as Master_Month
,Week(TempDate) as Master_Week
,Date(MonthStart(TempDate), 'M-YYYY') as Master_MonthYear
,Weekday(TempDate) as Master_WeekDay

;
Load
MinDate + IterNo() -1 as TempDate
While MinDate + IterNo() - 1 <= MaxDate;
Load
Min(FieldValue('Canonical_Date', RecNo()))-1 as MinDate
,Max(FieldValue('Canonical_Date', RecNo())) as MaxDate
AUTOGENERATE FieldValueCount('Canonical_Date');

topic Canonical Date associations problem in App Development

Canonical Date associations problem