Do query calculations take on the context of queries they are joined to?

[psrpsrpsr]

Hi folks, I have a use case that has me wondering about the unintended consequences of RUNNING-TOTAL() that can result in joining queries.

Scenario:
I have two queries:
> Query 1 aggregates "Actions" (which is a fact) to a YearMonth dimension (date attribute).

YearMonth    Actions Count
201509           1,100
201510           1,175
201511           12,450
201512           1,200

> Query 2 displays store locations that are officially open. Locations in various non-open operative stages get a timestamp of 1900-01-01, so I have two fields that output:

YearMonth    # Open Locations*
201509           200
201510           210
*# Open locations  is calculated as: RUNNING-TOTAL(CASE WHEN [Location open date] <> 1900-01-01 THEN 1 ELSE NULL END)

> The two queries are joined on YearMonth with a cardinality of 1..1 = 1..1.

Problem:
The trouble is, there is an operational consideration that creates location outliers that I want to operate out of the equation. Hypothetically, let's say a franchise location moves down the street, and the POS vendor considers their move from one location to the other as 'actions', incorrectly inflating the Action Count, as shown above in Query 1's 201511 row.

So to remedy the situation, I dragged [Location ID] into Query 1 in order to create a query calculation to null out outlier values, so the output is:
Yearmonth     Location ID    Actions Count (no operation)       Actions Count (nulled out outliers)
201511          01234            11,500                                          NULL
201511          05432            125                                               125

The inclusion of Location ID in Query 1 explodes the number of rows in the joined query of Query 1 and Query 2. What I think is happening is that Cognos is applying the RUNNING-TOTAL from Query 2 to every combination of YearMonth and Location ID in Query 1!

My questions are:
a.) Am I able to simply do a LEFT JOIN, and return the values of Query 2 where they match in Query1?
b.) When you join queries in Cognos, do the respective query calculations from each query then combine, and their prior individual context becomes combined with the other query? Perhaps this is what is leading to the unexpected outcome.
c.) Does this have to do with combining queries from different 'grains'?

Thanks!

[bdbits]

Yes you can do the equivalent of a left join by setting the cardinality of the join properly.

It's like your (b) question - the queries will each bring back their results, and those are joined much like tables would be. The caveat is that this will be done locally on the Cognos server. For large tables this can obviously have a significant performance impact on your Cognos server.

I think your issue is with the way you joined queries, not running-total.

You might want to peruse the latter parts of section 10 in the user guide.