If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Calculation between Two Queries

Started by cognostechie, 03 Sep 2010 02:41:46 PM

Previous topic - Next topic

cognostechie

I am trying to create a Crosstab report that would show 2 measures and one calculated mesaure showing the difference between both the measures. Problem is I cannot use both the meausres in one query so I have to use two seperate queries. So, one measure is in Query 1 and another measure is in Query2.

This is what I am trying to achieve (Final Result):

                              Tent Gear           Camping Equipments       Other Items
Italy
Netherlands
Canada
Total                
Last Year's Total
Difference


This is what I am trying to achive. I have 2 queries. Query 1 has all of the above but only Data for current year. Query two has exactly the same items on rows and columns but the measure is Last Years's data.

How can I do a calculation to subtract Current Years's data from Last Year's data. I am using a Relational package. I tried creating a Third query and joining Query1 and Query2 but it doesn't work
. Any suggestions?



Forgot to add that the problem here is to get the 'Difference' in rows !

cognostechie

Got it to work !

Create a 3rd query by joining the first two, include all the data items from both, don't put anything in the measures and add both the meausres to the rows, then create the calculation (Diff) between both the measures.

However, with this scenario, if I do a Total of the first or second query, it doesn't work. Wonder why? Not a big deal, because I have the totals in the form of summary in the 3rd query! 

That being said, if anybody has a better solution, it is welcome !