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.