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');
