Adding two singletons

[erics]

Hi there,
I have a report that contains two seperate singletons. One is the sum of sales, the other the sum of quotas.
Is there an easy way to subtract the two?
I've tried to subtract them, but I keep getting a cross join error, even though I've already created a relationship between the two tables (1-M)
The two tables do not have the same number of records, so I can't just do a join and subtract the two.

Eric

[colt]

Hi,

if I understand you right, you have a Report with 2 queries out of which you take out the 2 values SumofSales and SumofQuotas into the singletons.
Then it should be possible to do this:

Subquery 1:     Item SumofSales=0;                 Item SumofQuotas=sum(Quotas)
Subquery 2:     Item SumofSales=sum(Sales);  Item SumofQutoas=0

Query3=Subquery1 Union Subqery2

Item SumofSales=coalesce(Subquery1.SumofSales, Subquery2.SumofSales)
Item SumofQutoas=coalesce(Subquery1.SumofQutoas, Subquery2.SumofQutoas)

This way you have both values within 1 query and you can calculate with the dataitems.

[erics]

I've tried to use a union. It's my first time using one in Cognos, so I may be doing something wrong, but it's throwing an error saying that one of the filters on Query1 does not exist.

[BigChris]

Hi Erics,

Just for the sake of clarity, what you're putting into the two subqueries are two data items and not filters.

In Subquery 1 you need a data item into which you're just going to put zero, and you're going to call that SumOfSales, and the second data item is going to be called SumOfQuotas and the calculation for that is going to be Sum([Quotas]). Follow the same approach for subquery 2 and the rest of Colt's instructions and you should get the results that you need. If not just post back.

(and apologies if I've just told you the blindingly obvious...just thought it might be worth going into a little more detail)

C

[colt]

Hi Erics,

just to be sure:  If your original Report consists out of Query1 and Query2. Query 1 includes queryitem SumofQuotas= sum(Quotas) and Query 2 includes queryitem SumofSales=sum(Sales).

Then create Query3 with only 2 items in it:  SumofQuotas=Query1.SumofQuotas; SumofSales=0.
Then create Query4 with only 2 items in it:  SumofQuotas=0   ; SumofSales=Query2.SumofSales

After this you can create Query5  = Query3 UNION Query4.

[erics]

The issue is that I've been tasked with creating a dashboard that's dynamic.
So there are dropdowns with a number of filters such as region, and date.
So, both of the queries have filters on them, and I'm trying to use the sum of both in a formula.
The only way that I can think of doing that is something crazy like doing a full outer join on two fields that don't match, or better yet calculated fields that we know will never match and sum up the whole thing ...