Week of Previous Year

by martin.matejka@keboola.com

GD isn't working that great when you are really interested in weekly data. Especially when you want to display the same week of previous year or compare this week with the week one (or more) year ago. That's caused by the Date Dimension schema.

At first, let me say that GD maybe isn't handling this well, but GD support team members know their stuff - thank you GD folks (especially Zuzka Bednářová) with helping me with this :)

So, in a perfect world, where you work just with monthly values, you would reach for the most well known magic MAQL words For Previous. But you're not living in the perfect world and you want to see data sliced by weeks. When you would say SELECT metric For Previous(year,1), you would end up with empty column, if the data are sliced by weeks.
But as almost any tricky task related to dates, this can be accomplished by using Disconnected Date Dimension. Let's look on two examples.

Weeks of whole year
Your goal is to select a Year on a dashboard and the report should show values for this and previous year sliced by weeks.
The desired output should look like this:

And how the metrics should look like?

This Year:
SELECT Metric WHERE Year (Date)=((SELECT MAX(SELECT Year (DDD) BY Date (DDD)) BY ALL OTHER EXCEPT Date (DDD)))

How does it work - calculate the metric for the year of related date dimension, where the year equals the year selected from the Disconnected Date Dimension dashboard filter.

Last Year:
SELECT Metric WHERE Year (Date)=((SELECT MAX(SELECT Year (DDD) BY Date (DDD)) BY ALL OTHER EXCEPT Date (DDD))-1)

It's almost the same metric, but as you probably now, dates, month, years etc. can represented by numbers in GD, so the only thing you need to do is to substract from the DDD Year.

And that's it!

One particular week
Your goal is to select one week and show value for this week and the same week a year before.

The desired output should look like this (there's been custom date dimension implemented, so don't be confused with Dec dates shown as week no. 1):

The core of the metric is the same as in the previous example, but you need to reflect week as well.

This Year:
SELECT Metric WHERE Year (Date)=((SELECT MAX(SELECT Year (DDD) BY Date (DDD)) BY ALL OTHER EXCEPT Date (DDD)))
AND Week (Date) = ((SELECT MAX(SELECT Week (DDD) BY Date (DDD)) BY ALL OTHER EXCEPT Date (DDD)))

Last Year:
SELECT Metric WHERE Year (Date)=((SELECT MAX(SELECT Year (DDD) BY Date (DDD)) BY ALL OTHER EXCEPT Date (DDD))-1)
AND Week (Date) = ((SELECT MAX(SELECT Week (DDD) BY Date (DDD)) BY ALL OTHER EXCEPT Date (DDD)))

Against the first example, you're also saying that week should be the one selected in DDD filter (which is locked on weeks).

Now you're fully protected against week related mind blowing bullets your customers or other end users will shoot at you and your GD skills will impress even more girls or boys at BI analysts parties!
Comments