on - edited on by
slosada
This article demonstrates how to visualize year over year comparisons. This will be useful if you want to compare the performance of multiple years by the same day, month, or quarter. This will require the creation of one column in your ElastiCube.
In the end, your report will look something like this:
Implementation
Custom Column- Date for Year over Year
This implementation requires one custom column, which will map every date that you're analyzing to a single year. In this case, this new column will duplicate the existing date, but will swap in the year 2012 for the actual year. I'm using 2012 because it is a leap year, and using a non leap year could cause the system to try and create non-existent dates. This new field will be used for the x-axis in the widget.
I've attached a .ecube file to use as reference. In it, there is a field called [Date for YoY], which has the following definition:
CreateDate(2012, GetMonth([OrderDate]), GetDay([OrderDate]))
For your implementation, just change the column [OrderDate] to be whatever date field you're analyzing. Then, build the change into the ElastiCube.
Creating the Widget
Start by creating a line chart. Select Month in Date for YoY for the x-axis, whatever calculation you'd like to use for the value, and then use the Years in your original date field for the Break By.
At this point, the widget will look mostly correct. The only issue is that the months are all displaying the year 2012, which is misleading to users. Click the calendar icon for Month in Date for YoY to format the date. Then, go to the custom format field, and enter the month format that you wish to use. I chose MMM. After making this change, the widget will be complete.
Extending this for Fiscal Years
If your company uses a financial calendar that doesn't start on January first, you may want to have this timeline match the way you look at a year. For this example, the financial calendar we're using starts on October 1st.
Custom Column- Date for Financial Year over Year
This implementation will be similar to the one above, but it will utilize a case statement to determine the year to use. In this example, we want the calendar to run from October 1st, 2011-September 30, 2012. This will map the months in the correct order, and will keep the leap year date in in 2012.
In this implementation, we're going to convert the month and day to a single integer, which will aid us in our comparison. (E.g. October 1st is 1001). This will be especially important if your financial calendar doesn't start on the 1st of a month. The code I used is in the attached .ecube in the column [Date for Financial YoY]. Here is the code being used:
CASE WHEN GetMonth([OrderDate])*100+GetDay([OrderDate])>=1001 THEN CreateDate(2011,GetMonth([OrderDate]),GetDay([OrderDate])) ELSE CreateDate(2012,GetMonth([OrderDate]),GetDay([OrderDate])) END
After implementing this new column, build the ElastiCube.
To augment this implementation, I used similar code to determine the fiscal year. This is in the column [Fiscal Year], and it uses the following code.
CASE WHEN GetMonth([OrderDate])*100+GetDay([OrderDate])>=1001 THEN GetYear([OrderDate])+1 ELSE GetYear([OrderDate]) END
Creating the Widget
The widget setup will be the same as above. Since Sisense orders dates by month and year, the dates will appear in the correct order on the x-axis. You may want to use the fiscal year identifier in the break by, rather than the normal calendar date. Here's the final output for the widget.
Download:
