Year over Year Calculation

[buddtholomew]

I would like to know whether the below scenario is achievable in Report Studio

Scenario:

DATE               2012    2011   VAR
10-01-2012        500K   300K   200K
10-02-2012        200K   100K   100K

I would like to see the Revenue for 2012 by day, the corresponding Revenue for the same day in 2011 and also the Variance. In the above example, for 10-01-2012 the Revenue amount is 500K and for 10-01-2011 the Revenue amount is 300K. The year over year variance for 10-01 is 200K.

Thanks in advance-
Budd

[wyconian]

Hi

You should be able to do that.  Are you using a cube or a relational source?

If you're using a cube you have probably got date attributes that can give you current year and previous year measures, you can drag these onto a crosstab and calculate the variance.

If you're using a relational source you will probably need a query to give you current year, another query to give you the previous year values.  Join the queries together and use the joined query as the basis for your report. 

Hope that helps

[buddtholomew]

Thanks for the response, but what would I join the queries on? Query1 is filtered for days in 2012 and Query2 for days in 2011. Revenue and Variance are not possible join conditions.

[MFGF]

Hi,

Extract the month and day parts of the date in each query, and use these as the join. You can use the extract() function for this.

Regards,

MF.

[buddtholomew]

If I join on Month and Day, will I still be able to show the data by date, e.g. 10-1-2012, 10-2-2012, etc as shown in the original post?

Thanks for the help.

[wyconian]

What are you showing the revenue for?  e.g. is it for a product group or a customer or division etc?

Both the sub queries should have the same dimension attributes (product, customer or whatever) and you should join the queries on these items, possibly with an outer join if the dimension has current year values but not previous year values.

[buddtholomew]

It is only Current Year - Previous Year Revenue growth. Thats what is making this so challenging.

[wyconian]

OK in that case do what MFGF says but have the extract as a calculation in the queries.  The calculation should show the day and month parts of the date e.g. 01-10 etc.  If you then join on the queries on these items but also have the actual date on the report you will be able to get the dates on the same line.

You may end up with having the day/month as a row heading and then the year as a column so you will get something like this
                  2011        2012
01/01          1234        5678
02/01          5678        1234
03/01          6589        7778

Then you can calculate the variance

(I'm English, our date format is dd/mm/yyyy rather than mm/dd/yyyy  :) )

[buddtholomew]

Thanks, this is exactly the path I was headed down. Hopefully concatenating the text '2012' to each row date will achieve the desired outcome.

One more question on this topic. I assume this approach will not work if I am interested in calculating YoY variances for the same day (7-31-2012 and 8-02-2011). Do you have any suggestions on how to accomplish this calculation?

[wyconian]

Are you saying you want to compare values on 2 differnet dates? 

Can you define some kind of rule that you can code into a data item?  If you can you can join on that.  You need to be able to join the 2 queries on some common field.

[buddtholomew]

I ended up joining the queries on Fiscal Week and Week Day to accomplish the desires behavior. Thanks for all the help.

[Brundy]

good info. 
i have tried this myself, but i'm not getting the proper aggregation in my join.  the aggregations are correct in each of the queries, but when i join them it throws them off. 

also, in each of the queries (Current YeaR and Previous Year) i can create a list and do a calculation in the list.  however, after i join the two and and use the new joined query, this option is no longer available.  any thoughts? 

thank you.