Linking Budget to Actual

[#1]

I am creating a QV model which interrogates our General Ledger.

I have successfully brought in the actual transactions from summary and detail GL tables.

I am having conceptual problems bringing in the budget. It is in a separate table and clearly at a different level, i.e. at cost centre / subjective code level, whereas the actual is at transaction level. What is the best way to link these two tables in conceptual terms?

As it stands I have 2 tables (act, bud) which are synthetically linked due to the need to link both GL code and the period.

Thanks in advance.

[#2]

can you post an example of your qvw file?

i'm assuming you want to use aggregate functions to sum the actuals up at a level that you can compare with the budget?

If so you should be able to join the tables together, display the budget and compare with an aggregate of the actuals...

[#3]

Hi Simon

You might want to have a look at this in order to remove your synthetic keys: http://community.qlik.com/media/p/76744.aspx

[martin59]

Hi Simon,

You must link your tables with only one common field.

Two tables are linked with fields which are the same name.

If you have several common name fields, do this :

1. If the field is not the key or a part of the key : rename it differently with the AS syntax.

2. if the field is a part of a composed key, you have to use one of these functions : concat, hash128, autonumber, or autonumberhash128.

3. Use the function with all fields which compose your key and in the both tables. Comment the fields used in your function in one of both table to delete you synthetic keys.

Martin

[tanelry]

Concatenate the budget table to transactions.
This is the best solution in most cases. Easy to connect to same calendar or other dimension tables, easy to handle changes (like additional number of linked levels).