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!!
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])
Thanks for your reply!!
This calculation not working, can you suggest any more please!!
Quote from: blom0344 on 06 Jul 2008 10:48:59 AM
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])
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..
Try week number functionality.
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:
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
_add_days(any date,-91) this function is working fine..
Thanks for all your time and responses!!
Quote from: blom0344 on 07 Jul 2008 01:43:50 PM
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:
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