#69 📆 Same Day previous year comparison in Qlik Sense

While working on a retail dashboard, a common analysis is to compare over previous year with a same day comparison. Not the same date late year, but the same day of the week.

A verified formula is to do: Date-364. Stephen Redmond blogged about it years ago if you want more explanation.

Let me give you an example of the clothing business. January 4, 2020 is a Saturday while January 4, 2019 is a Friday. The activity is very different between these two days, and therefore, the comparison from one year to the other is distorted if we reason in "date". So I want to compare it with the same Saturday of the previous year.

You’ll say:” Ok Christophe, then I just need to use some Set Analysis like Sum({$<Date={$(=Date-364)}>} Sales)”

Yes, but in fact no 😉

What if you want to display a line chart with Date as dimension and showing the current and previous year with two lines? You can’t…

The trick, is to do it in the script by creating what I call a “pivot table” between facts and Calendar like this :

Load script to generate it from Facts :

Pivot:
Load distinct Date_ID as Date_ID, 
	 Date_ID as Date_Cal,
	 'P' as TypeDate
resident Sales;
Load distinct Date_ID-364 as Date_ID, 
	 Date_ID as Date_Cal,
	 'SameDay' as TypeDate
resident Sales;

By adding this table, you can link a Date from Calendar to several rows in the fact table. This trick can be use for every date comparison you’d like to achieve, but that’s another story !

A really simple way to use it in your dashboard. All you have to do is to use TypeDate in your set analysis like this :

Current Year : Sum({$<TypeDate={'P'}>}Sales)

Same Day previous year : Sum({$<TypeDate={'SameDay'}>}Sales)

I’m doing some Qlik coaching actually with different clients. And I often redirect them to some tips and tricks I share here. It seems to be a good idea to keep posting some specific concepts and keep a record for the future.

🎁 You can download QVF