What function do I need to use to calculate 13 prior weeks in Report studio

[cognoshelp@ymail.com]

Hello All Cognos Gurus,

I need to calculate a date column.

I am getting a date values from Data base eg. MM/DD/YYYY HH/MM/SS

What I need is 13 prior weeks from the value of database.

eg

If Database value is 07/05/2008 20:07:58

I need to get 13weeks before data i.e 05/05/2008 20:07:58.

Is there any formula you can suggest me to use?

I will be glad if you send me directly the formula...please its very urgent.

You can directly reach me @ cognoshelp@ymail.com!!

[blom0344]

You need to wrap your own logic with _add_days / _add_months functions or by using a calendertable.
For SQL server you may want to check out if the dateadd T-sql function is supported in Cognos:

dateadd(ww,-13,[somedatetime])

[cognoshelp@ymail.com]

Thanks for your reply!!

This calculation not working, can you suggest any more please!!

You need to wrap your own logic with _add_days / _add_months functions or by using a calendertable.
For SQL server you may want to check out if the dateadd T-sql function is supported in Cognos:

dateadd(ww,-13,[somedatetime])

[blom0344]

13 weeks = 3 months? If that isn't exact enough you may need a calendertable that stores weeknumber and day_of_the_week number to define the exact calculation..

[cogman]

Try week number functionality.

[blom0344]

week number itself is not enough as it cycles through the values of 1-53 for each year. For any broken year this will not work (week 4 -13 = week 44 of the previous year)

I think a pretty neat solution would be to use a calendertable with columns for offset of 13,26  etc weeks.

For SQL server this would be a piece of cake using:

dateadd(ww,-13,[somedatetime])
dateadd(ww,-26,[somedatetime]) expressions

Example:

Code Select Expand


Insert into table calender_new (date1,date_offset13,date_offset26)
select date,dateadd(ww,-13,date),dateadd(ww,-13,date) from calender


This would work equally well in a view-definition on a calendertable

[cognoshelp@ymail.com]

_add_days(any date,-91)  this function is working fine..

Thanks for all your time and responses!!

week number itself is not enough as it cycles through the values of 1-53 for each year. For any broken year this will not work (week 4 -13 = week 44 of the previous year)

I think a pretty neat solution would be to use a calendertable with columns for offset of 13,26  etc weeks.

For SQL server this would be a piece of cake using:

dateadd(ww,-13,[somedatetime])
dateadd(ww,-26,[somedatetime]) expressions

Example:

Code Select Expand


Insert into table calender_new (date1,date_offset13,date_offset26)
select date,dateadd(ww,-13,date),dateadd(ww,-13,date) from calender


This would work equally well in a view-definition on a calendertable