How to Compare Periods like Easter v Easter LY
If you are working in Retail, Wholesale or any Supply Chain organisations, there will be times of the year that are critical to your business in terms of sales. These could be periods like Christmas, School Holidays and Easter – especially if you are a chocolate manufacturer or retailer.
Whilst some of these critical periods are set in the calendar as the same date every year, like Christmas, some very definitely aren’t – like Easter for example. Easter is a moveable feast but always falls between March 21st and April 25th. It’s calculated as the first Sunday after the first full moon following the first day of Spring.
“Like for Like” may be different dates, weeks or even months
So when it comes to comparing “Like for Like Sales” using normal Period comparisons like YearMonth or Week No, Easter may be in different weeks and even different months, year on year.
This causes enormous headaches for sales managers who are trying to compare sales, and manufacturers whose “Easter” production dates might fall “n” number of weeks before the actual retail sale dates.
Even the Christmas period may overlap weeks differently, year on year. And there may be some other key dates that might be local to your business which change from year to year, like festivals or weather related events.
Use Diver to create an easy to use template for comparing non-calendar periods
So what’s an easy way in Diver to get an exact period comparison, that doesn’t use either weeks or months?
Below I show you how to set up a Time Series in ProDiver that allows you to set a Start and End Date for two time periods based on Date. When you save the marker on the dates you have selected it will open on those same dates each time. You can also use it over and over for different dates on an ad hoc basis as well as saving specific markers for specific dates.
You can even keep the marker open but close the current window and dive on different dimensions – or dive from one dimension down to another level. The usual Diving techniques apply.
Step by Step Guide for creating a universal date comparison marker.
Step 1.
Open your model on a suitable Dimension
Step 2.
Open the Time Series Control Panel from the Edit Menu and click Add.
Step 3.
Make sure you have a Time Dimension selected that is based on a date rather than a period like YearMonth or Week.
Step 4.
Select the Template “Today vs Yesterday” and click Add in the top right. This will populate the Periods and the Column Types automatically.
Step 5.
Select “Current Period” and click Edit.
Step 6.
Edit the Current Period so that you are using Pick Date and Create Start Quick View for both Start and End.
Step 7.
Do the same for Previous Period but note you can leave the -1 Days as it will not be used.
Step 8.
I changed it to 0 just because I like to have unneeded information removed. It helps when going back to review the set up.
Step 9.
Choose the column you want to compare. Click Finish. Remember you can choose more than one or use the technique I shared in my previous blog for swapping Values.
Step 10.
Click OK.
Step 11.
You are now left with the default dates you set up in the control panel, and all the columns you had plus the Time Series Columns. As the default dates are all the same for the moment, you are comparing 1 day against the exact same day.
Step 12.
Amend the dates to compare two periods with start and end dates – here it is Easter 2014 v Easter 2013. (Good Friday to Easter Monday). Delete the unwanted columns.
Step 13.
Amend the Column Headings to a more usable format.
Step 14.
Format as a Report with whatever colours and styles you prefer – you can used a saved template if you use them.
As I said above:
When you save the marker on the dates you have selected it will open on those same dates each time. You can use it over and over for different dates on an ad hoc basis as well as saving specific markers for specific dates.
You can even keep the marker open but close the current window and dive on different dimensions – or dive from one dimension down to another level. The usual Diving techniques apply.
Good luck and Happy Easter!
About Debbie Lonsdale
Debbie Lonsdale has been working with the Diver Solution as a BI Consultant at Dynamic Business Informatics since 2008. Her previous experience includes computer programming, analytical and technical roles, team management, account management, sales and marketing in a variety of market sectors, including the Travel Industry and Distribution. She combines this experience as an all round ICT professional in the BI sector.