I am currently doing a cross tab report with two query. Query 1, will show all the total revenue for this CurrentYear-Month. As for Query 2, it will show the total of previous year total revenue.
Query 1:
Product Total Revenue
Jan-2013 XXXX 123,456
Feb-2013 XXXX 999,999
Mar-2013 XXXX 888,888
Query 2: Product Total Revenue
2012 XXXXX 1,234,567
Problems:
I have try to do union, but it say query in-compatible. I try join, it gave me wrong total whereby it triple up the last year value.
If anyone can show me how to to combine query 1 and query 2 by showing the end result such as below will be great.
Output Result expected:
Product Total Revenue
Jan-2013 XXXX 123,456
Feb-2013 XXXX 999,999
Mar-2013 XXXX 888,888
2012 XXXX 1,234,567
I just had a very similar problem -
While there might be a way to do it in a cross tab, I ended up using a list and pivoting the data in the query, then union the results.
There might be issues with this approach if you have an unpredictable product list, or a long list of products though.
What are the data types for the first column in each query? I suspect first is a string and second is a number which is why union won't work. Try casting 2012 to a string.
Sent from my iPhone using Tapatalk