Budget vs Actual Link Table

[#1]

Hello Community,

I have a financial dashboard which has a primary fact table
(Actual) with financial information as well as a budget fact table (Budget).
The primary fields in the budget table that I need linked to the financial
table are:

1. AccountingYear
2. AccountingPeriod
3. LocationCode
4. DepartmentCode
5. AccountCode

I have linked the two tables together using fields 3-5 in a link table with
the three fields as a concatenated key, and can create a table showing budget
vs actual information with Department as a dimension. I did this by putting in
set analysis BudgetAccountingPeriodAndYear =
{$(max(ActualAccountingPeriodAndYear))}.

I only used fields 3-5 in the key because when I added fields 1 and 2 to the
key, QlikView would not display the budget amount if there was no actual amount
for that accounting period and year. However, how do I make this work so I can
show a graph of budget vs actual lines or bars with a date as a dimension?
Right now if I use the date from the Actual table, then all Budget information
is displayed going back to the earliest date while Actual information is
displayed correctly. Please let me know if you can help.

Thank you!

[Gysbert_Wassenaar]

You should try to consolidate both fact tables into one. See this blog post: Fact Table with Mixed Granularity