COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: tushar gaurav on 25 Jun 2015 04:24:43 AM

Title: How to extract week from date
Post by: tushar gaurav on 25 Jun 2015 04:24:43 AM
How we can extract week from date i.e. if today is 25thJune2015 so how we can find this date belongs to which week of month??
Title: Re: How to extract week from date
Post by: Lynn on 25 Jun 2015 05:16:52 AM
Look at the calendar on the wall, find the desired date, start counting at the first week and then stop when you get to the week containing the desired date.

All kidding aside, if you want do this in an automated fashion you will need to provide more details.

Do you have a relational package or dimensional? If relational, what DBMS? Does your definition of a week run from Monday to Sunday or from Sunday to Saturday or something else? How do you intend to handle partial weeks where there are dates in the week that span two months?

Assuming relational, you might be able to look at the example below as a starting point that you can refine further around your specific requirements.

The extract function gets the day number for the date in question. This number is divided by seven because we have seven days in a week. The floor function takes the integer portion only (part to the left of the decimal) from this calculation to which we then add 1 to get the week number.


floor ( extract ( day, [Calendar Date] ) / 7 ) + 1

Title: Re: How to extract week from date
Post by: bdbits on 25 Jun 2015 09:08:05 AM
Again assuming relational, if you will be doing a lot of date calculations I highly recommend you build a time table with a lot of precalculated attributes for each date. Then use this table as a foreign key reference wherever you have a date. For example, for each day you might have the date, day of the week, day name, whether it is a work day or a holiday, and calculations for the calendar and fiscal week, month, quarter and year, etc. This becomes immensely useful for report authors and makes filtering on a day of the week incredibly simple.
Title: Re: How to extract week from date
Post by: Lynn on 26 Jun 2015 02:23:38 AM
Quote from: bdbits on 25 Jun 2015 09:08:05 AM
Again assuming relational, if you will be doing a lot of date calculations I highly recommend you build a time table with a lot of precalculated attributes for each date. Then use this table as a foreign key reference wherever you have a date. For example, for each day you might have the date, day of the week, day name, whether it is a work day or a holiday, and calculations for the calendar and fiscal week, month, quarter and year, etc. This becomes immensely useful for report authors and makes filtering on a day of the week incredibly simple.

So true!!
Title: Re: How to extract week from date
Post by: rockytopmark on 26 Jun 2015 08:59:34 AM
Most Important Dimension in any Data Warehouse (IMNSHO):

Date
Title: Re: How to extract week from date
Post by: cognostechie on 26 Jun 2015 12:51:32 PM
So true ! 

I recently worked for a company which follows a fiscal calendar different than the regular calendar. They made a slight change to their calendar this year but also wanted to see the results if they had kept the same calendar. I implemented a system that would let them run two calendars at the same time and every report can run for any of those calendars. Couldn't have been possible without creating some fields in the Date Dimension table. The prompt macro helped a lot in this too.
Title: Re: How to extract week from date
Post by: bdbits on 26 Jun 2015 03:29:05 PM
I would agree that date/time is often the Most Important Dimension in any Data Warehouse.

I am currently working in US state government with a legislature that meets only every other year and has its own fiscal year (July 1). So, I have multiple time ranges for many subject areas - calendar year, state fiscal year, federal fiscal year, and biennium (the last is most often a top level in the other hierarchies). We have months, quarters, weeks, etc. precalculated for all of them. If modeling dimensionally, I most often use alternate drill paths in a single hierarchy that drills down to the day. It works out very well, actually.
Title: Re: How to extract week from date
Post by: bdbits on 26 Jun 2015 03:35:38 PM
I started thinking about this so I checked one of our warehouses in the date dimension table. It has 57 columns. Seems like a lot to me.  ;D
Title: Re: How to extract week from date
Post by: cognostechie on 26 Jun 2015 04:00:24 PM
Yes 57 is a lot. If it is absolutely required then it is ok otherwise it is better to clean it up because the more columns it has the slower it will become.

In some cases those columns might be required like marking marriage anniversary, girlfriend's birthday, entering old age etc  ;)
Title: Re: How to extract week from date
Post by: bdbits on 26 Jun 2015 05:30:42 PM
They are actually required. We use a lot of conformed dimensions, so there are a lot of different needs across the subject areas.

is_bdbits_anniversary is not one of the columns, though.  :P