DATETRUNC by decade

I recently wrote a post describing how to allow users to decide the level at which their time-based data was displayed: year, quarter, month, week, day etc.

The solution uses the DATETRUNC function. However one weakness of this is that it does not allow decade-based reporting. The DATETRUNC function only goes as high as the YEAR unit.

This evening, I had a desire to allow the user to report at either year or decade level. So I set out to tackle the problem.

In the parameter field (described in the aforementioned post), I added the ability to select Decade. And I used the following formula to define the reporting field:

if [Date unit] = “decade” then str(year([Date])-(year([Date]) % 10))+”s”
elseif [Date unit] = “year” then str(year([Date]))
end

The initial clause is invoked when when Decade is selected. The last part, featuring the % symbol, is the equivalent of the MOD function in Excel. It divides the year of the date by 10 and brings back the remainder.

So if you have a date in the year 1987, it’ll do the calculation 1987 – 7, bringing back 1980. The “s” is strung onto the end to make “1980s.

If you need the field to appear as and be treated as a date, then things get a little more tricky. Unfortunately, you can’t use the DATETRUNC feature against the parameter, as whenever Decade is selected, it’ll break, even if that clause isn’t being invoked. (Rather annoying.) And so you’ll need to use an IF THEN ELSEIF to populate based on each of the parameter’s values:

IF [parameter] = “Week” then DATETRUNC(‘week’,[Date])

ELSEIF [parameter] = “Month” then DATETRUNC(‘month’,[Date])

ELSEIF [parameter] = “Year” then DATETRUNC(‘year’,[Date])

ELSEIF [parameter] = “Decade” then date(“01/01/”+str(year([Date])-(year([Date]) % 10)))

END

Not the most elegant. But it should do the trick.

Leave a Reply

Your email address will not be published. Required fields are marked *