last_day function in cognos

[niahanth]

Hi

I have a requirement in which i need to last day of the previous week in the report.

For example if i run the report today i need to show the date as 1/31/2016

I am writing using last_day function but i am getting error.

I need to calculate last_day(sysdate ,-1 week). It calculates the last day of the week and subtracts 1 week and displays the date.

Thanks

[MFGF]

I am writing using last_day function but i am getting error.

What expression are you using? What error do you get? Are you using a relational or a dimensional package? When does a week start and end - Sunday to Saturday or Monday to Sunday (or something else?)

MF.

[niahanth]

Hi

I am using relational model.

The week starts from Monday and ends on sunday.

I am wriritng
last_day((sysdate)-7 days))

I am getting the following error

V5 syntax error found for data item 'Data Item1' of query 'Query1', invalid token "sysdate" found after "last_day((".

Thanks

[schrotty]

Hi,

Instead of sysdate try current_date (it's the cognos build-in-function).

The other syntax also doesnt look like cognos-syntax

I'd use something like

Code Select Expand

_add_days( current_date, _day_of_week(current_date,1)*-1 )

Schrotty

[BigChris]

As an aside, you might want to consider building a Calendar table for your data warehouse. In there you can store dates for yesterday, start of week, start of last week, end of last week, start date of current financial period etc.