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.

[#2]

I have made progress. What STILL DOESTN" WORK is at the bottom of this entry.

WHAT WORKS:

I have gotten the DateBridge to work (from ).  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:

[#3]

Nav,

Would that one fact table be created manually or out side of Qlikview?  That would be a onerous task.  I get 100's of transactions a month.

But it could be done if all else fails.

- Karen

[#4]

I tried combining BLI and Budget in the same table.  That didn't help.

- Karen

[nav_pienaar]

No, you will have to do it as part of transformation process in QlikView.

The best way to test your own logic and to get help on it if it doesn't work, is to build a small sandbox app with just the key fields and a hand full of record.

Nav Pienaar

[#6]

nav.pienaar,

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.

[#7]

Final working table scheme