How to show week-by-week comparisons in grouped vertical bar cards where the weeks roll-over years?

When I got into work this morning, the Grouped Vertical Bar cards which I had built to show week-by-week comparisons of THIS year's values versus LAST year's values were all broken.

The Beast Modes that I had created to differentiate by week were all malfunctioning because today is Wednesday, January 2, 2019, and the end of "Last" week was Monday, December 31, 2018.

The issue was that I needed to represent each week independently, and also sequentially, so that week 52 of 2018 will preceed week 1 of 2019.

The easiest "fix" that I could construct was to think of the week number for each year as a strictly numerical value -- rather than a string to be sorted -- so that they could be displayed left-to-right across the x-axis.

Here's a sample of the part of the Beast Modes that I created to accomplish this:

/*  To compare the `date` of any row of data to that of the CURRENT_DATE()  */

(abs(year(`date`))*52 + abs(weekofyear(`date`))) = (abs(year(CURRENT_DATE()))*52 + abs(weekofyear(current_date())) -2)

/*  Accepting data where the date is from TWO Weeks ago!  */

As you can see, each week number is calculated as the sum of the absolute value of the result of the "weekofyear" function plus 52 times the absolute value of the result of the "year" function - so that Dec 2018 is equal to 104948 internally, and Jan 2019 is likewise equal to 104949.

While any constant can be used in order to sort months BY ORDER, using 52 as the constant allows them be ordered SEQUENTIALLY.


I hope that this helps someone else as much as it helped me!

Respectfully,

Dean T. Pangelinan
Marketing Data Analyst – Technical Solutions, Global Marketing Analytics
Robert Half