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 !
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 !