FormatDate WebIntelligence Function Error

[Former Member]

Hello

I have 2 date objects (start date and end date).

I need to be able to replicate the DAYS360 function that is in EXCEL. I have SQL code that allows me to do this and have been able to create a number of variables in my WebIntelligence document in order to return the correct values.

The first steps/variables to replicate the DAYS360 function that I am using are:

Get the Day from the Start and the End Dates

Day_Of_Start_Date: FORMATDATE(startdate; "dd")

Day_Of_End_Date: FORMATDATE(enddate; "dd")

Now Set these to a Maximum of 30 (since the DAYS360 function assumes no more than 30 days in a month)

Set_Day_Of_Start_Date: If Day_Of_Start_Date = 31 Then 30 Else Day_Of_Start_Date

Set_Day_Of_End_Date: If Day_Of_End_Date = 31 Then 30 Else Day_Of_End_Date

Next, substract the Start Date day number from the End Date day number

End - Start = TONUMBER(Set_Day_Of_End_Date) - TONUMBER(Set_Day_Of_Start_Date)

Next, workout the months between the start date and the end date

Months = MONTHSBETWEEN(Start_Date; End Date)

Next, multiply the months value by 30

30*Months

Finally calculate DAYS360 as

DAYS360 = [30*Months]+[End - Start]

All this works perfectly well when the browser locale is English, but when the browser locale is German an error #ERROR is returned in the WebIntelligence document. I cannot understand why.

I think that I am going to have to create the objects in the Universe rather than use variables in the WebI document. But I still want to understand why this does not work for a German Locale.

Note that both date formats (English locale and German locale are dd/mm/yyyy)

Please can you help.

Thanks in advance

Julian

[Former Member]

Hi Julian,

This is the behavior of the product; The #ERROR usually indicates an incorrect format entered in the formula to the format which is expected according to the locale used.

The date format might be same for English and German locale but the date seperators are different, loke for english it is '/' and for German it is '.'

Regards,

Nakul Mehta

[Former Member]

Hello

Thank you. I have formatted the date in the first step using FormatDate(<date>; "INPUT_DATE") and this works correctly now.

Perhaps someone could help me with another question on the same topic/document.........

I need to calculate the Year End Date from the Start Date. For example

Start Date = 12 Jul 2011

End Date = 11 Jul 2012

Year End Date = 31 Dec 2011

I've tried a number of different ways to do this but all fail for different reasons. For example:

ToDate("31/12/"+FormatDate(Start_Date; "yyyy"); "INPUT_DATE") works for UK but not for German locales

ToDate("31.12."+FormatDate(Start_Date; "yyyy"); "INPUT_DATE") does not work for UK locale

ToDate("31122011";"INPUT_DATE") returns #ERROR

Please can you advise?

Thanks in advance

[Former Member]

There are few settings in your webi Preference Tab which we need to take care.

Just check what preference you have under

Web intelligent > When viewing a Document

Use the document locale to format the data

- If this is selected than what ever the Local setting for the Document creation system it will come

Use my preferred viewing locale to format the data

You can view data as per your local setting

Idly it should be u201CUse the document locale to format the datau201D.

Try this and see if this can help you out.