Forecast vs. Shipped Volume - Joining 2 Tables

[danielle_v]

Hi All,

I need to do some analysis around forecasted vs. shipped volumes. The shipped volumes are basically the sum of units shipped by month and country.

The forecasted volumes are read in from an external Excel spreadsheet, and are also set out in a similar way, showing the forecasted units by month and country.

I am trying to join these 2 tables together using a key based on month and country, so that I can output the figures as follows, showing the forecasted vs. shipped volumes by country and month;

Country	Month	Forecast	Shipped
India	May 14	1000	700
China	May 14	2000	1500
Turkey	May 14	800	900

The problem comes where there is a country/month combination in one of the tables but not the other - for example if there was volumes forecasted to ship from China in Jun 14 but nothing shipped, or vice versa. Basically, I need to show all combinations of month and country, whether they appear in one or both of the tables. I believe if I left join one table to the other, I will only get values for the country/months that appear in the first table?

Is this possible?

Any help greatly appreciated!

[#2 · Accepted solution]

Hi Danielle,

you want to concatenate your two tables together rather than join. Load each one with a measure type field (for Shipped and Forecast). Then you can use that measure type field within each of your expressions to give your value split (or pivot and drag measure type up within your table)

Edit:

Fact:

load

Country,

Month,

Amount,

'Forecast' as Measure_type

From xxxx

Concatenate (Fact)

load

Country,

Month,

Amount,

'Shipped' as Measure_type

From xxxx

hope that helps

Joe

[danielle_v]

That does indeed help... many thanks!

[#4]

no worries, glad to help