Grouping by months...

[Chris_]

Hi,

I'm looking for a solution to what may be a simple problem although I'm struggling to figure it out.

Basically I use Cognos as the reporting tool for our Global HR system.  The field for this example I am using is Termination Date and I'm looking to total the employees for each month based on this.

Each employee/record has a unique identifier which is Global ID.  So I have made a list with the first column being Termination Date and the second column being a Query Calculation which is a count of Global ID.

So when I run the report, I get a list of dates when employees were terminated and a list of 1's where a terminated employee has been counted.

What I wanted was a list of months, and the number of employees for that month.  So I changed the Data Format of the date so it displays the month only and got the same result as before, although rather than the list of dates I got a list of months.  I then grouped the column and got the same results.

Any suggestions what I could do to product the desired results?  I am a fairly new Cognos user and have only recently completed the IBM training.

Many thanks,

Chris.

[MFGF]

Hi Chris,

I think you need a query calculation to extract the year and month from your Termination Date, and then do your grouping on this.

The syntax you need will be along the lines of:

extract(year,[Your Termination Date Item]) * 100 + extract(month,[Your Termination Date Item])

This will give results such as 201006 (for this month).

Best regards,

MF.

[Chris_]

Hi MF,

Thanks for your speedy reply!

I don't think we covered the extract function in our training - it sounds very handy.  Are we able to extract the month only?  The report I am creating will be used for statistics on a quarterly basis... if it only displays months and not years I'd be happy with that.

Thanks, Chris.

[Chris_]

After some further "tinkering" I have made progress.

I have extracted the month only, which gives me the numbers for the month.

I then created another query calculation using a case statement to name the months... however when I run the reports this is sorted alphabetically ... is it possible to sort this by number/date?

Thanks, Chris.

EDIT - I forgot I could sort by the previous data item (Numbers for the month) and the problem is resolved.  Many thanks for your help.

[MFGF]

I don't think we covered the extract function in our training - it sounds very handy.  Are we able to extract the month only?  The report I am creating will be used for statistics on a quarterly basis... if it only displays months and not years I'd be happy with that.

Hi Chris,

There are various date parts you can extract using the extract() function.  When you are defining a calculation, click on the Fx tab on the left to see a list of functions, expand the Common Functions folder, then expand the D-F subfolder.  Click once on the extract function in the tree, and you should then see the help information displayed on the right, which gives you an idea of how the function works, and what parts you can extract.

If you end up extracting the month number only, do be aware that if you group on this, it will ignore years and give you summarized numbers for each of the 12 months spanning all years for which you have data.  This is why my suggested syntax had you extracting both the year part and the month part and combining them together.

Best regards,

MF.

[Chris_]

Hi MF,

Thanks for your help again and pointing this out, it could cause problems at a later point in time.

I will make a change to my query calculation to use your format, as going forward this will be the better option.

One observation that I have made, is that I have a slight problem with dates.  Basically the report starts off with a prompt page that asks you for a date range... so I put in 01/01/2010 - 31/03/2010 and then run my report.

On my main report, I have 3 queries that base their data on the date entered in the prompt ... each of them are returning a day or twos data for April also ... do you have any immediate thoughts as to what this could be?

Thanks, Chris.

[MFGF]

What kind of package are you using - relational (with Query Subjects and Query Items) or Dimensional (with Dimensions, Hierarchies, Levels and Members)?

How are the filters on each of the queries defined?

Regards,

MF.

[Chris_]

Hi MF,

I'm not 100% sure on which package we are using?  I have 3 queries in the report and each query uses data items from our package?

The filters on each query are set up in the following manner...

[HRMS (Active)].[Position Detail].[Effective Date] in_range ?LeaversByReasonDate?

The [Position Detail] changes in each query, to return the correct data within the dates originally selected on the Prompt Page.

The parameter for the Date Prompt on the Prompt Page is LeaversByReasonDate.


Thanks, Chris.

[MFGF]

Hi MF,

I'm not 100% sure on which package we are using?  I have 3 queries in the report and each query uses data items from our package?

The filters on each query are set up in the following manner...

[HRMS (Active)].[Position Detail].[Effective Date] in_range ?LeaversByReasonDate?

The [Position Detail] changes in each query, to return the correct data within the dates originally selected on the Prompt Page.

The parameter for the Date Prompt on the Prompt Page is LeaversByReasonDate.


Thanks, Chris.

Hi Chris,

If you have query subjects and query items visible in your package, then you are using a relational package.

One thing to check - you appear to be filtering on Effective Date in each query - can you confirm which date item is being displayed in the report?  I'd assume that it can't be Effective Date, or else the filter would eliminate all dates outside the range 1st Jan 2010 to 31st March 2010.

Regards,

MF.

[Chris_]

Hi MF,

It sounds like we could be using a relational package.

I've linked two screenshots, one shows the filter I've used in the query and the other shows the parameter on the date prompt - can you see anything from this that may help indicate the problem I'm having?

http://www.upload3r.com/serve/210610/1277132945.jpg

http://www.upload3r.com/serve/210610/1277132905.jpg

[MFGF]

Hi,

If I'm reading this right, you have two different date items - Termination Date and Effective Date?

Your first filter is returning all people with a Termination Date set, and your second filter is returning all people with an Effective Date between two prompted ranges.

You indicated in an earlier post that your reports are returning dates in April, despite the prompt values being selected as 1st Jan 2010 to 31st March 2010.  The big question is...  which date item is being displayed in your report - the Effective Date or the Termination Date (or another date altogether)?

My guess is that your filter is based on a range of dates for Effective Date, but the report is displaying a different date.  Have a look at the items being used in your report (list, crosstab, chart etc) to see which date item is actually being included in the display.

Best regards,

MF.

[Chris_]

Ah, yes.  I've put in both Termination Date, and Effective Date.

I think there may be an inconsistency in our system data here that could be causing the problem I'm facing.  I think I should consider removing the effective date, and basing the results on Termination Date alone to get a solid answer?

[kumar8123]

 Hi All
I didn't find the icons like calendar and clock in 'my folders' to run my report, any idea please?

[MFGF]

Hi All
I didn't find the icons like calendar and clock in 'my folders' to run my report, any idea please?

Does this relate to Chris's problem?

If not, you should probably start a new thread and provide some details of what you are trying to do.

MF.