Categories
Technology

Surface Curious

A brush with a Lumia, a less than stunning version of office for the mac, a need to do some stuff with Access for the rugby club I’m secretary for, and the intrigue of pen input has lead me to wanting to try a Microsoft Surface. I’ve ordered a Surface 3, 4gig ram and 128 gig hard drive. It is waiting for me when I get home, so thoughts on it will follow.

Categories
Cognos

Reporting on activity by week

This is a rewrite of this post as i completely failed to understand what i’d written. The original text is left after the break for completeness.

A week is a unit of time that has to be derived, and therefore has multiple meanings. Some business will run Monday to Friday, others will run Sunday to Saturday. To calculate the week, you need to know what date the first day of that week occurred on. This is actually quite easy to derive as you will know what day of the week the current day is. If you are running a Monday – Sunday week then if today is Tuesday, it is day 2 of the week, therefore the date the week starts will be 1 day ago. If the day is Thursday, this will be day 4 of the week, therefore the date teh week starts will be 3 days ago.

To achieve this is Cognos Report Studio we will use two funcitons.

The first function is

_day_of_week ( date_expression, integer )

The first parameter is the date you want to know the day of week of. The second paramater is the day that starts the week. 1 = Monday, 2 = Tuesday, etc. So in this case we will use the value 1. This will return the day of the week.

The second function is

_add_days ( date_expression, integer_expression )
This take the first parameter, and adds to it the number of days sepcified in the second. If you prefix the second paramater with a minus (-) then it will remove that number of days.

So to work out the week number, we need to take the

Day of the week, and remove that many days from the current date. However  as the first day of the week is day 1, and not day 0. So to compensate for this we need to add another day on.

The final formula would be
_add_days(cast([Transaction Date],date),-_day_of_week([Transaction Date],1)+1)

Categories
Excel

Date tricks in Excel – Latest Month, and preceeding 12

Assuming you have your data formatted as a table (if not, why not) then you can use the following technique to see if it is the most current month for that set of date or if it is in the most recent n number of months.

For this example we assume that you have a simple list comprising of

  • Customer Name
  • Date order placed

You want to see the orders that were placed during the latest month you have data for.

The first thing will be to have a column to represent the month which is just a date format of the first of the month. This could be done in the formula later, but this reduced the complexity of the subsequent formula in that you always know that it will be the first of the month you are dealing with. Assuming you had a date in B1 (which is set to 29 June 2015), the formula to give the first of the month would be:

=DATE(YEAR(b1),MONTH(b1),1)

The date takes three parameters of year, month, and day and converts that to a date serial number, but instead of numbers we have put more functions. The year function returns the year of a date serial number as a four digit number.
The month function returns the number of the month of a date serial.
The final parameter is set to 1 – which puts the day element as the first of the month.
So with the date as 29/6/2015 the function evaluates to:

=DATE(2015,6,1)

With this column in place and called month. we then use either of the following techniques.

Working out if Current month.

To work out if the row is in the current month you can use the following formula.

=IF([@[Month]]=MAX([Month]),"Latest","Previous")

This compares the value of month in the row with the highest value in the entire month column. IF they are the same then the formula returns the string ‘Latest’ if not it returns ‘Previous’.

Working out in preceeding n months.

To work out if the value is in the preceeding n months, then use the following formula.
=IF([@[Order Date]]>=DATE(YEAR(MAX([Order Date])),MONTH(MAX([Order Date]))-13,1),"Previous 12","Older")

Categories
Cognos

Minimum date in Cognos Report Studio

I have a list containing

[Customer ID]
[Transaction ID]
[Transaction Date]
[Sales Team Name]
[Purchase Type]

I want to have a list that gives me the date of earliest transaction for each customer. Filtering the report on:
[Transaction Date] = minimum ([Transaction Date] for [Customer ID])
will give me the earliest date the customer had a transaction.
The only issue with this is where the customer has two transactions on their first day. That’s the bit i’m working on. Any help or advice would be greatly appreciated.

Categories
Cognos

Converting YYYY-MM-DD to YYYYMMDD in Cognos Report Studio

How to convert standard SQL date format to YYYYMMDD

right(year([Gym].[Member Details].[DOB]),4)+
right(month([Gym].[Member Details].[DOB])+100,2)+
right(day([Gym].[Member Details].[DOB])+100,2)
Categories
Cognos

Resizing value prompt boxes in Cognos Report Studio

Thanks to the following site http://cognospaul.com/2012/01/10/quickie-dynamic-select-and-search-size/ for the follwoing.

To resize a value prompt insert a snippet of html before it with the following

<divid="selectSearch">

Then another html snippet afterwards with the code below

</div>

var e=document.getElementById('selectSearch');
var myselect = e.getElementsByTagName('select')[0];
if(myselect.childNodes.length>0){myselect.style.width=''}

This will then resize the element automatically within the page (just don’t ask me how)

Categories
Excel

Financial Year in Excel

This formula will give the financial year a date occurred in.

This assumes that the date is in A1.

=IF(MONTH(A1)<4,YEAR(A1)-1&"-"&RIGHT(YEAR(A1),2),YEAR(A1)&"-"&RIGHT(YEAR(A1)+1,2))

This can be altered for acadamic year by replacing the 4 with a 9 (academic months start in September, not April)

Categories
Cognos

Comments within Cognos Report Studio

You can add comments In expressions by using the following

;#/*your comments here*/#;

As with any programming the better documented your report and code is, the easier it is to maintain.

Categories
Cognos SQL

Automatically Reporting on Previous Calendar Month

This little snippet is used in a filter and automatically looks at the previous calendar month.

cast([Sales Team].[Client Contacts].[Start Date],date)
between
_first_of_Month(_add_months(CURRENT_DATE,-1))
and
_last_of_month(_add_months(CURRENT_DATE,-1))

If the date is left as a timestamp then the last of the month is 2013-04-30 00:00 which means that a client contact that occurs at 9am would not be picked up. Casting it as a date ensures that it gets picked up as expected.

Another option is to add 1 day onto the _last_of_month, but that’s a hack.

The _add_months function can accept a negative number, which turns _add_months into _substract_months.

Categories
Cognos SQL

Formatting an ID Number

This snippet takes an ID number and displays it in the format of 3-3-4 (e.g. 123 456 7890)

substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),1,3)+' '+
substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),4,3)+' '+
substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),7,4)+' '

Its a simple substring operation, withthe account number being cast as a 10 character string