Just Another Date

Well there's lucky, Justin had dates in mind and so did I! I have been fortunate to
enough to have easy access to W_DAY_D and a plethora of pre-calculated YTD and
account period end figures so all plain sailing.
As we are working on Analytics for a Siebel Financial Services implementation we
have a large number of growth comparison figures and related % figures, all fairly
easy maths, divide one by the other and multiply by 100. If you are lucky enough the
have most of the figures pre calculated this is a quick and easy task, open the LTS
for your table, create a new column, open the expression builder and create you
expression to calculate the percentage. But beware, although analytics appears to
accept quite complex statements here, there are a few pitfalls, Siebel 7.8 does not
support nested aggregation statements so you can't for example do a SUM(Case.../
SUM(case....where ..Count(...... x100.
It will let you do Count(Case when...........) / Count(case when.....) x 100, but I
would recommend you avoid this, you may well find that if the resultant column is
included in a report with other similar columns the resulting SQL can look very odd
indeed and may not be an accurate reflection of what you originally intended, this
appears especially true if the report also includes other columns sum or count
aggregation set. Look at the SQL issued via your Admin/Manage Sessions option to see
the resulting SQL.
I would recommend creating seperate calculated columns and then a final column based
on them, to provide the result from a simple expression, this may add a small
processing overhead, the sql will be a little longer, but it will be clearer and
easier to trouble shoot. You will also have reusable metrics that may be of use in
further reports or perhaps to your client should they implement ad-hoc reporting in
the future.
Next up was small issue concerning the year, or last year to be precise, as our
current project is Siebel Financials, we started off with variables for this year,
last year, two years ago and so on, whilst these appear to offer everything you
might need, beware. Come the first week of January and you may think 'hang on a
minute, this says Dec '07, '06 and '05', this is the consequence of a report that
uses THIS_YEAR, LAST_YEAR and LAST_MONTH from the date variables to report on the
last complete month and previous years. The reporting is always a month behind, so
in January '07 they are reporting on Dec '06, the solution of course is easy, and
there are probably many variations on the theme, we have used a case statement added
to the Initialization block to recognise when the month is January, eg
Case when CURRENT_MONTH='1' then YEAR(GETDATE())-1 ELSE YEAR(GETDATE())
Use this for suitably named variable such LAST_REPORT_YEAR, if you need to report
further back use the VALUEOF(LAST_REPORT_YEAR)-1 etc to ensure the other years are
in sync with the current year.
The final part of these date related fun and games has been creating a report that
looks 3 months into the future, a rare requirement judging by the various message
boards etc which are stuffed with date range queries relating to periods past. We
decided to use more variables, 1 because we like variables and 2 because should the
business ever decide they wish to report on 6 months in the future they can either
edit the init block or use it as a template to create a new range.
We used the following query in our init block and created two variables called
THREE_MONTH_START and THREE_MONTH_END;
SELECT Q1.ST , Q2.END FROM
(SELECT CONVERT(DATETIME,(LEFT(CONVERT(VARCHAR(10),GETDATE(),112),6))+'01') AS ST) Q1,
(SELECT DATEADD(DAY,
-1,DATEADD(MONTH,+3,(LEFT(CONVERT(VARCHAR(10),GETDATE(),112),6))+'01')) AS END) Q2
As an alternative to this you could of course use similar SQL in an expression based
filter for dates between X & Y.

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.