Calculation of Date type was giving weird Result
While working in calculation of dates in Webi report I came across a situation where
the “ToDate” function in Webi was not giving the result as expected. While doing some reasearch
on this understood that there was simple mistake happened and it can happen by anyone.
Would like to explain which is the mistake has done by general user/developer and
solution for this.
Scenario:
– One date is passed to the report as a prompt value and other date comes from the
Universe Object or query result.
1. MeetingDate is a prompt
2. Expiry date is an Object
Need to calculate the difference between these two dates. We can use DaysBetween
function in Webi.
Error: If I pass MeetingDate = 24th Oct 2012 (24/10/2012) while converting this string value into Date format it was
giving me the result as 24/01/2012.
Solution:
As the MeetingDate is a prompt its data type is “String” so we need to convert this String into Date format.
For this we can use ToDate () function and we have to pass the correct format of the date.
Mistake:
– While converting the MeetingDate into date I used the following function
- ToDate(UserResponse(“EnterMeetingDate”),”dd/mm/yyyy”)
- In above syntax if I use “mm” and it gave me wrong result e.g. 24/01/2012
To get the correct result we have to use the correct syntax for month and it is like
“dd/MM/yyyy”
When I used ToDate(UserResponse(“EnterMeetingDate”),”dd/MM/yyyy”) I got the correct
result as 24/10/2012.
Hi Swati,
Please convert the "Meeting Date" object properties to Date in the universe,as it might help the users navigate through the dates in ascending or descending in the prompt list order rather than browsing through entire list. This is just an option.
If you have access to Universe Designer, you can convert the object format to which ever you would like to present the users or report developers.
Regards
Leela
Thanks for you response Leela.
In my case MeetingDate is the prompt created at Report level.The object is not present in the Universe.
Swati
If possible convert at the universe level (Meeting Date) to date field, as it might be helpful for other users who create their own reports and have no problem in future.
This is just an idea and better way to handle the future requests from business users.
Thanks,
Leela
I think it would give #ERROR.