cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

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:

Community_Admin_3-1634202642999.png

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 in the month format that you wish to use. I chose MMM. After making this change, the widget will be complete.

Community_Admin_4-1634202643026.png

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 one. Here's the final output for the widget.

Community_Admin_5-1634202643052.png

Download:

YearOverYear.dash

Year Over Year.ecdata

Version history
Last update:
‎10-14-2021 02:11 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request