0

How do I write a formula for the last day of the current year (i.e. 12/31/2022) in Web Intelligence Business Objects?

Kaleb Simmons
  • 1
  • 1
  • The easiest way would be to concatenate "12/31/" to the year extracted from the current date. Since they Year() function returns a number you need to use the FormatNumber() function to convert it to a string before appending "12/31/" to it. Finally, if you want to use it as a true date you will need to convert the resulting string to a date with ToDate() function. What have you tried? – Isaac Aug 15 '22 at 17:24
  • I created a formula for the first day of the current year (1/1/2022), using this formula: =ToDate("01/01/"+FormatNumber(Year(CurrentDate());"##");"dd/MM/yyyy"). I then tried doing the same for the last day of the year by changing "01/01/" to "12/31/", but receive #ERROR. – Kaleb Simmons Aug 17 '22 at 13:25
  • You are SO close. It looks to me like you have your "dd" and "MM" reversed. It works for 01/01/2022 because the day and month are the same. I doesn't work for 12/31/2022 because "31" is not a valid month. You may also need "####" in your FormatNumber() function so you have a 4 digit year rather than a 2 digit year since you have "yyyy" in your ToDate() function. – Isaac Aug 17 '22 at 14:07

2 Answers2

1

You can also add random "date" object to universe and replace SQL script for that object to:

trunc(add_months(sysdate,12),'YYYY')-1

and you will get the last day of current year

bekart
  • 66
  • 3
1

=ToDate(FormatDate(CurrentDate();"yyyy")+"1231";"yyyyMMdd") will work.

It takes the CurrentDate(), gets the year, concatenates the year and then converts the string 20221231 back to a date.

Mark
  • 61
  • 2