Simple Chart - Forecast and Actual Paid does not work

[#1]

Your help would be very much appreciated.  I have, what I think is, a simple setup with Budget line items (BLI), Statements of work (SOW, aka Purchase Order) and Transactions (Trxn, aka Paid).  The Bugdet includes, for each BLI, a monthly budget and forecast.  All the input is XLS.

When I create a QV table of sums by year, the yearly sum are not correct – the total is correct.

If I add a column to my Trxn table, TrxnYear, I can get the table above to work BUT if I add sum(TrxnAmount) to any other table, it has the same error.

Below is my data structure, the budget XLS and excerpts from a table box that lists the transactions.  I have included my MasterCalendar script also.   Below the structure are excerpts from a table box that lists the transactions demonstrate the issue…see Year in the last column (this is before I added TrxnYear).

Budget table (XLS)

When I ask to see Just 2016

When I ask to see just 2017

When I ask for ALL years (these dups don’t appear when I remove the Year column)

These dups don’t appear when I remove the Year column.

[vinieme12]

Are you filtering on the correct year?

TrxnYear from Trxn table should be filtered on not Year from Master calendar .

I think your budget table has same blin numbers for multiple dates across both years

Create a composite key with a combination of blin and date to connect budget and Sow tables

[#3]

vinieme12‌

I am using TrxnYear, I think.  I'll have to see.  how can I use a different year in the same table - as in BudgetYear for the Budget sum and TrxnYear for the Trxn sum?

[#4]

Are my answers in Canonical Calendar and/or HERE?

If so...having difficulty implementing a Canonical Calendar.

[#5]

Forgot to include my Master Calendar Script

MinMax:
LOAD
        Min(BudgetDate) as MinDate,
        Max(BudgetDate) as MaxDate
RESIDENT Budget;

LET vMinDate = Num(Peek('MinDate', 0,'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0,'MinMax'));

LET vToday = $(vMaxDate);

//*********Temporary Calendar*********
TempCal:
LOAD
        date($(vMinDate) + rowno() - 1) As TempDate
AUTOGENERATE
        $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*********Master Calendar*********
MasterCalendar:
LOAD
        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
RESIDENT TempCal
ORDER BY TempDate ASC;

DROP TABLE TempCal;

[vinieme12]

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

[vinieme12]

Even better would be to create a link table with a  master calendar

Sow >> Trxn >> Link table with keys from both tables + Calendar fields >> Budget table

Linking to two or more dates

[#8]

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.

[#9]

I've worked thru a few options - learning now the mapping and link table work.  So now I think I understand what you are suggesting.  I will try that tomorrow.  And let you know!

[nav_pienaar · Accepted solution]

Hi Karen

Try to get all the Facts in one fact table. The granularity could be different from for Actual transactions, budget & forecast. Having one fact table makes everything easier on the front end.

Hope this help you.

  Nav Pienaar