Date Formats in the Analytics Application

Justin Townsend, a member of the Majendi team has some useful advice…

Dates are often a point of consternation – for users and developers alike – in the
analytics application, but there are many reasons why they are used. Dates and
their derivations are often drawn from the warehouse table W_DAY_D, but what do you
do when this table isn’t available in your subject area or your repository?

This post gives an example of how dates can be manipulated to provide the format you
need. Additionally, the example format below can be used to sort data correctly at
this level of aggregation (Year/Month).

Take the example of a warehouse table with a date present, e.g. W_ASSET_D.END_DT.
The corresponding column in Answers could simply be ‘End Date’.

Select the ‘End Date’ and hit the Results tab, you probably get something in a
format similar to this: 09-Sep-07. The format isn’t so important here. When you
make changes to the date from Answers, the function is shipped down to the database
in any case. Its important though, to get your syntax right.

So we’re going to create the format YYYY / MM from the date 09-Sep-07. You need a
second column in your request that you can use to create the new format, any column
will do.

We can break this date function up into a few sections:

– we need the 4-digit year (YYYY), so we can use the standard Analytics function
called Year and we get a syntax like:
Year(End Date), which gives you 2007

– then we need the constant ‘/’. It looks like a bit of space either side would
make it more readable, so be sure to include that. So now it probably looks a bit
more like ‘ / ‘.

– next we need the 2 digit month (MM), so we can use the standard function called
Month and the syntax looks like this:
Month(End Date), which gives you 9. That’s OK, but if the date was in October then
it would be 10 so it doesn’t look even. We straighten that out later on.

– then we need to bring the 3 parts above together using concatenation. The CONCAT
function will help us here. It only likes string formats so we need to convert the
date bits to text, something like:

– CAST(Year(End Date) as VARCHAR(4))
– CAST(Month(End Date) as VARCHAR(2))

N.B. The month has been cast as width 2 to account for 10, 11, 12.

Once we’ve cast the dates, then we can add them to a CONCAT function below:

CONCAT(CAST(Year(End Date) as VARCHAR(4))|| ‘ / ‘, CAST(Month(End Date) as
VARCHAR(2)))

You’ll notice a couple of things about the expression, we’re using some
double-pipes and there’s only 1 comma. The Analytics CONCAT function works only
with 2 strings separated by a comma, so CONCAT(‘String1′,’String2’). The way to
add more sections to the string is by using the || (double pipes). There appears
to be no limit on the use of double pipes.

So this format is beginning to look pretty good, for 09-Sep-07, I now get ‘2007 /
9′. It’s not quite perfect though, with dates in a list it will not look even when
compared with date ‘2007 / 10’:

2007 / 9
2007 / 10

We can make a change to the string after the comma to add a leading zero to all
months that aren’t 2-digits long.

– so a CASE statement is needed to complete the format to our satisfaction:

CASE WHEN Month(End Date) < 10 THEN CONCAT('0', CAST(Month(End Date) as
VARCHAR(1))) ELSE CAST(Month(End Date) as VARCHAR(2)) END

To finish this off, we insert it into the original CONCAT statement after the comma
and before the final ‘)’, the resulting expression is:

CONCAT(CAST(Year(End Date) as VARCHAR(4))|| ‘ / ‘, CASE WHEN Month(End Date) < 10
THEN CONCAT(‘0’, CAST(Month(End Date) as VARCHAR(1))) ELSE CAST(Month(End Date) as
VARCHAR(2)) END)

Now all our dates look the same, ‘2007 / 09’ and ‘2007 / 10’.

If you know you’re repository developer well, ask him to add the logic to the
repository. That way you won’t have to reproduce it every time you need it.

Look out for more on dates in the future.

Thanks to Justin Townsend for this blog.