Total of Previous Year and CurrentYear-Month Total in Crosstab Cognos

[XtremeProgrammer86]

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

[calson33]

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.

[Lynn]

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