on 06-01-2015 12:43 PM
Hi experts,
i am trying to use the Bex query date prompts in Webi reports,
i have the prompt name as Approved date, it is showing the correct result if i used the below mentioned formula directly
Formula Used =UserResponse("Approved Date")
Received Output:12/29/2014 3:11:00 PM;6/1/2015 3:10:19 PM - (expected and also correct one)
i want to do a customization of dates here, , i am trying to get an output as
From Date: 29/12/2014 and ToDate : 1/6/2015
Tried formula
=FormatDate(ToDate(UserResponse("Approved Date"); "MM/dd/yyyy hh:mm:ss A"); "dd/MM/yyyy") + " and " + FormatDate(ToDate(UserResponse("Approved Date"); "MM/dd/yyyy hh:mm:ss A");"dd/MM/yyyy")
i am received the output as #ERROR
i am not sure how to use the two different dates here, date values are displayed as start value and end value, i here with attached the filed details
kindly share your inputs to solve this error,
i am using BO 4.0 SP10.
Regards,
Balaji
Use one d and one M in your ToDate conversions - MM expects 06, not 6
So you'd have
=FormatDate(ToDate(UserResponse("Approved Date"); "M/d/yyyy hh:mm:ss A"); "d/M/yyyy") + " and " + FormatDate(ToDate(UserResponse("Approved Date"); "M/d/yyyy hh:mm:ss A");"d/M/yyyy")
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hang on, I've seen the problem.
Your UserResponse string spits out one long string right?
What you'll need to do is create two variables that hold the from and to dates:
Create an initial UserResponse variable, Approved Dates as:
=UserResponse("Approved Date")
Now create variables Approved Date From and Approved Date To as:
Approved Date From:
substr([Approved Dates];1;Pos([Approved Dates];";")-1)
Approved Date To;
substr([Approved Dates];Pos([Approved Dates];";")+1;Len([Approved Dates])-(Pos([Approved Dates];";")+1))
Display those two variables and see if they return what you want to convert. You may need to tweak the +1 values to be +0 instead. Once they look like you want, use the following:
=FormatDate(ToDate(UserResponse("Approved Date From"); "M/d/yyyy hh:mm:ss A"); "d/M/yyyy") + " and " + FormatDate(ToDate(UserResponse("Approved Date To"); "M/d/yyyy hh:mm:ss A");"d/M/yyyy")
Hi Mark,
For Approved Date From: -
substr([Approved Dates];1;Pos([Approved Dates];";")-1)
Error: Invalid character ';' at position 50. (IES 10080) (WIS 10080)
For Approved Date To
substr([Approved Dates];Pos([Approved Dates];";")+1;Len([Approved Dates])-(Pos([Approved Dates];";")+1))
Error as :
Invalid character ';' at position 50. (IES 10080) (WIS 10080)
I can able to validate the formula in the variable editor without any issue, but when I clicked the ok button for submission, I am receiving this error, and the screen is closed,
not able to find the exact reason for this erro, Need your inputs
just noticed similar kind of requirement discussed in this thread, but I am not able to apply the given answer, just spend few mins on this thread and if you understood and related to this requirement/ issue then kindly guide me http://scn.sap.com/thread/3227081
Regards,
Balaji
Hi Balaji ,
Please use following formula
A=UserResponse("Approved Date")
Date1=formatdate(todate(substr([A];1;pos([A];";")-1);"MM/dd/yyyy hh:mm:ss A");"dd/MM/yyyy")Date2= formatdate(todate(substr([A];pos([A];";")+1;length([A]);"MM/dd/yyyy hh:mm:ss A");"dd/MM/yyyy")Let me know it will resolve your issue or not.Regards,Anish Mahadikhi Anish,
Thanks for your inputs, I have faced some parenthesis issue in Date2, then I have added one close braces at the end of the length, then I can able to get the expected output
updated Date2 Formula
Date2= formatdate(todate(substr([A];pos([A];";")+1;length([A]));"MM/dd/yyyy hh:mm:ss A");"dd/MM/yyyy")
with the help of you and Tanveer my issue got resolved, just want to understand for performance wise issue will arise if we use more custom formula or variable, what kind of things we need to note and take care for performance perspective.
Any personal experience/inputs then kindly share the same.
Hi,
Use the below formula
= "From Date: " + Substr([Formula Used];1;10) + " and To Date : " + Substr([Formula Used];23;30)
Thanks,
Tanveer
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi Tanveer,
still same error
as per your inputs I tried the formula as
= "From Date: " + Substr(FormatDate(ToDate(UserResponse("Approved Date"); "MM/dd/yyyy hh:mm:ss A"); "dd/MM/yyyy");1;10) + " and To Date : " + Substr(FormatDate(ToDate(UserResponse("Approved Date"); "MM/dd/yyyy hh:mm:ss A");"dd/MM/yyyy");23;30)
Regards,
Balaji
hi Tanveer,
Based on your input I can able to get the From Date as expected, I think substring 1-10 digit exactly matching, but for Todate it is coming along with time, tried the substring range even 24-26
Formula used: = "From Date: " + Substr(UserResponse("Approved Date");1;10) + " and To Date : " + Substr(UserResponse("Approved Date");24;26)
Output:
From Date:12/22/2014 and To Date:12/26/2014 11:10:19 AM
In this substr option I noticed that if i give single digit date as a input option, then partial time also included here,
for the same formula, for single digit date output as
From Date:6/1/2015 6 and To Date:/2015 6:36:03 PM
we need to check alternate option here.
Regards,
Balaji
Hi Mark, Balaji,
Yes mark you are right here .
Alternatively you can use here =Pos([String];";") and Length( ) and use shifts in substring to get the desire result irrespective of date format
Ideally
Create a variable Date = UserResponse("Approved Date")
Pos( [Date];";") should be 22
and Length( [Date]) should be 43
So create 2 variables POS and LEN as formula above
POS = Pos( [Date];";")
LEN = Length( [Date])
you can shift the substring starting and ending positions parameter based on length and position of ";"
= "From Date: " + Substr([Date];1;10 - (22-[POS]) ) + " and To Date : " + Substr(Right([Date];21- (43-[LEN]) + (22-[POS]) );1;10 - (43-[LEN])+(22-[POS]) )
This should work perfectly.
Thanks,
Tanveer.
hi Tanveer,
I tried this given option, not showing the correct output, tried the single and double dated values,
it showing different results,
Formula:
= "From Date: " + Substr(UserResponse("Approved Date");1;10) + " and To Date : " + Substr(Right(UserResponse("Approved Date");22);1;11)
correct output showing for double digit date format,
but if I change to single digit date for the same formula, some unwanted formats are included here, it is slowing as
From Date:6/1/2015 3 and To Date:PM;6/1/2015
so I think as per Mark, this substr is not suitable method here.
Hi Balaji,
All you need to create is three variables Date of type Dimension and LEN, POS of type measures
Where is that you are getting the error ??
1. Date = UserResponse("Approved Date")
2. POS = pos([Date];";") this should be of type measure
3. LEN = length([Date]) this should also be of type measure
and then use the formula as suggested above
= "From Date: " + Substr([Date];1;10 - (22-[POS]) ) + " and To Date : " + Substr(Right([Date];21- (43-[LEN]) + (22-[POS]) );1;10 - (43-[LEN])+(22-[POS]) )
Let me know how this works
Mark,
Here we are using 22 and 43 as offsets not just hard coding , i have tried all possible cases and it is working perfectly.
Thanks,
Tanveer.
hi Tanveer,
Thanks for your continuous inputs for my request/issue, I can able to view the both single and double digit format.
but in the single digit format currently it is showing as From Date/Todate: 1/6/2015 , how to show is as 01/06/2015 , I want to add 0 in the single digits date format. where I can do customization here.
Regards,
Balaji
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.