Wrong results in a Join querry

[Hristina_Bambeva]

Hi all,

I'm using a relational package and I need to compare the data (travel expense )for the same month in 2 consecutive years, to calculate the percentage decrease/increase and then to display the 3 numbers in a crosstab.

what I came up with is to create 2 different queries for each month. Then I make a join query where I calculate the decrease/increase.

After that I make a union to be able to display the 3 results one next to the other in a crosstab.
The problem is that the result in the join query is incorrect. The cardinality is 1..1 = 0..1
The link is made on Profile Number which is identifying the client.

I'm not even sure if this is the correct approach, maybe there is another way to do it without a join query. I'm attaching the report specification and and image of the output I'm looking for (with the correct numbers)

Thank you so much for you help and suggestions!

[Lynn]

Hi all,

I'm using a relational package and I need to compare the data (travel expense )for the same month in 2 consecutive years, to calculate the percentage decrease/increase and then to display the 3 numbers in a crosstab.

what I came up with is to create 2 different queries for each month. Then I make a join query where I calculate the decrease/increase.

After that I make a union to be able to display the 3 results one next to the other in a crosstab.
The problem is that the result in the join query is incorrect. The cardinality is 1..1 = 0..1
The link is made on Profile Number which is identifying the client.

I'm not even sure if this is the correct approach, maybe there is another way to do it without a join query. I'm attaching the report specification and and image of the output I'm looking for (with the correct numbers)

Thank you so much for you help and suggestions!

I think you can do it in one query.

- Filter your query to include records from the month you want in both years. I don't know what your date dimension looks like or if you even have one, but something like this, except you would use expressions or prompts instead of hard coded values:

Code Select Expand


[Your Expense Date] between 2015-08-01 and 2015-08-31
or
[Your Expense Date] between 2014-08-01 and 2014-08-31


- Next you need a query item in your query with a case statement to bucket only the money for LAST YEAR into a single column.

Code Select Expand


case when [Your Expense Date] between 2014-08-01 and 2014-08-31
then [Your Expense Amount]
else 0
end


- Do the same thing again, except for THIS YEAR.

Code Select Expand


case when [Your Expense Date] between 2015-08-01 and 2015-08-31
then [Your Expense Amount]
else 0
end



- Now you can use those two query items to compute the difference and growth.

[Hristina_Bambeva]

Hi Lynn,


Thanks so much! I guess I was overcomplicating the report. I tested your solution and I'm getting the correct results.
Do you have any suggestion for the layout.
when I was using the union I had each measure(amount spent, amount, refunded, number of tickets, etc) in the rows and the period in the columns (Month this year, Month last year, % variance, YTD, previous YTD % variance).
I'm not sure how I can I achieve the same layout with having all the measures in the same query.

I hope my question is clear there is an image in my first post.
Thank you all for the help!