Adding values of two same data items from two different joined queries

I have 2 queries which give me data item "no. of products" by "month" but for old department name and new department name respectively.
Now I'm trying to join these two queries so that I will get val (departemtname) = val(olddepartment) + val (newdepartment).
The problem I am facing that when I am joining the two queries, it is showing me addition of values for only the months which have common. for Jan has 2 (olddepart) + 3 (newdepart) will give 5 in the 3rd query output but for Feb having 5(old) + 0 (new) will give null value.

What should I do to get the solution. I am assuming that I may need to use Full outer join, but how in Cognos 8.4?

If the department names are different, then how are you doing a join on them based on your current queries?
If you are doing a join, you still need something common between the old and new to get them to join up.
Are you using some other key?

So if you have a common name for each of the old and new (say A for A and A for A1) and this column is called ConformedDeptName in both queries,
then do a union of both queries (they both contain ConformedDeptName).
In the result of the union, add a dataitem that is defined as total(amount for ConformedDeptName). this should roll up all the data and give you a total for the departments based on the ConformedDeptName.

ARty,
You're close in what you are saying and just not saying it correctly.
Query 1 : A1, val1 , conformedDeptname
Query 2 : A2 , val2 , conformedDeptname

Union has a union between q1 and q2.
The columns in the result of the union would be:
company, value, conformedDeptName

You have 3 columns in each query, your union would then have 3 plus another which would be
total(val for conformedDeptName)

Actually, you could probably even have just 2 columns in your unioned query - conformeddeptname and value (which is a total).

Now when you report, your report would list out:
conformdedDeptName, total.

Is it making more sense?
Keep in mind that if you have 10 rows in q1 and 8 in q2, your union will have 18 rows.
What you do with them to combine (total) the data is up to you.
Remember each query has to have the same number and datatypes have to match for each column to do the union. (You will get an error if you mess that up)

When I am using join, i am getting the result but addition of product only for columns which has some data. If either product or goods is missing, it is showing null val. When both has value then only it is adding both values.

ARty,
Yes, as you describe what you want, the union should do it.
You might have to set the query in report studio to allow cross joins. Although I don't know why it would require this.
Set it to allow the cross join and try it as a union.

The join solution may work if you do a full outer join. You might have to account for the nulls and if null then substitute a '0'. If this is Oracle, you can put the following in each of the columns nvl(amt,0).

But the union should work if you set the query to allow cross joins. Try it and let me know.

You can model two BI-queries as a set operation and then access the in
another query where the layout
defines the grouping and you set your aggregation property.

The other approach using a join requires you to define a join on the
common_key and month but define the cardinality
as a full outer because you don't know which of the two queries might not
return a row for the given key and month.

The set operation may be faster in general.

If you decide to concatenate the two names use coalesce() to ensure you
don't try to concatenate a null value with a non-value which
results in a null-value.

Actually you would be better using the COALESCE() function than NVL()
even if your database is Oracle, since by the time you have got your
data into the queries it may no longer be using the database but its own
internal data structures so NVL() will fail.

If you create your UNIONS correctly then you should be able to get the
result your require. !