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