Forgot to include my Master Calendar Script
Min(BudgetDate) as MinDate,
Max(BudgetDate) as MaxDate
LET vMinDate = Num(Peek('MinDate', 0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0,'MinMax'));
LET vToday = $(vMaxDate);
date($(vMinDate) + rowno() - 1) As TempDate
$(vMaxDate) - $(vMinDate) + 1;
DROP TABLE MinMax;
TempDate As BudgetDate,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
'Q' & ceil(month(TempDate) / 3) as Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
Week(TempDate)&'-'&Year(TempDate) as WeekYear,
inyeartodate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,
inyeartodate(TempDate, $(vToday), -1) * -1 as LastYTDFlag
ORDER BY TempDate ASC;
DROP TABLE TempCal;
Instead of connecting trxn to >> Sow to >> Budget
Change it to Sow >> Trxn >> to Budget
Applymap Blino to Trxn table using purchaseorderid , drop the field Blino from Sow and let it connect to Trxn only with purchaseorderid
In trxn table Create a key with Blino & TranDate as Key
In Budget table create a key with Blino & Budget Date as Key
Rename the Blino field in both tables, Trxn and Budget table to avoid a synthetic key
I am going to try BLINo+Date to add an key to the forecast and remove Forecast to a separate table with no date. I don't want to add BLINo to the Trxn, as that would be only semi-automated. The automated extract includes SOW, but no reference to the BLI.
BLI have monthly budget amount and monthly forecast amounts
BLIs can have multiple SOWs. SOWs can have multiple transactions - sometimes more than one a month.
Your answer worked mostly. I did not need to create one fact table. With or without that the problems were the same. the only way I could get the association back that I needed was to live with a syn key. I had a developer in our office try and she could not find another way.
I have made progress. What STILL DOESTN" WORK is at the bottom of this entry.
I have gotten the DateBridge to work (from Canonical Date). I added the "TKey" manually to my files as I couldn't get ApplyMap to work:
My transaction now sum correctly. And I have a lovely Budget, Foreccast and Spend to date (Paid) chart.
WHAT DOESN'T WORK
However - I lost the association between transaction and Workstream. I have not been able to find a way to get it without generating a Loop or SynKey.
Here's my data model currently: