Skip to Content
Author's profile photo David, Choong Poey Yee

BW Report with This Year Date & Last Year Date measure comparison

Recently I have been assigned with a reporting requirement to compare measures (E.g. Sales Figures) from This Year’s date compared with the same date from Last Year’s. Below might give some idea of the reporting requirement or report format requires.

The reason for such reporting requirement is because the client need to compare how good the sales is compared with the same day of last year. This is to make sure that the marketing strategy could be in place or to discontinue certain line of products when there is a drastic change of the sales observed. Nevertheless there could be multiple ways to look at these data.

This Year Date Last Year Date This Year Sales Last Year Sales This Year MTD Last Year MTD
05.01.2015 05.01.2014 5,500.00 4,000.00 27,500.00 25,500.00
04.01.2015 04.01.2014 4,000.00 3,500.00 22,000.00 21,500.00
03.01.2015 03.01.2014 1,000.00 2,500.00 18,000.00 18,000.00
02.01.2015 02.01.2014 8,000.00 8,000.00 17,000.00 15,500.00
01.01.2015 01.01.2014 9,000.00 7,500.00 9,000.00


In order to create such a reporting requirements, a single InfoCube that contains the Day Sales are created. In this InfoCube there should be 3 different dates, that is the Current Date, Last Year Date and Next Year Date. These dates should be populated accordingly during data load.

In order to view the above mentioned report format we need to combine two different InfoCubes but in order to prevent data duplication of Previous Year data, we could instead create another InfoSet from the same InfoCube that we have created previously and named it as Sales Figure (Last Year). This eventually will be used in the MultiProvider that combines the original InfoCube and the new InfoSet created.

In the MultiProvider, it should combine 2 InfoProviders, that is the original InfoCube and the InfoSet. There within the MultiProvider should be the Current Date and the Last Year Date InfoObjects. Additionally, 2 sets of key figures should be included that is the sales figures from the InfoCube and InfoSet.

Assignment of the InfoObjects to the InfoProvider should look like below.

InfoProvider This Year Date Last Year Date This Year Sales Last Year Sales
InfoCube This Year Date Last Year Date Sales Figure
InfoSet (Last Year) Next Year Date This Year Date Sales Figure


In order to get the Last Year Sales figures from the InfoSet the “This Year Date” should be assigned to the Next Year Date InfoObject in the InfoSet and “Last Year Date” should be assigned to the This Year Date InfoObject in the InfoSet. In this manner we are able to read the Last Year Sales Figure without duplication the data in another InfoCube.

I hope my explanation of the above is details enough and clear for your understanding.

Best Regards

David Yee

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.