Formula below is off - what am I missing? When I use the formula without UserResponse it works as standalone but using UserResposnse, it ignores the formula and only bring the Budget AMT
=If UserResponse(“Prompt Text”)=FormatDate(CurrentDate();”Mmm yyyy”) Then ([Budget AMT]/DayNumberOfMonth(LastDayOfMonth(CurrentDate())))*DayNumberOfMonth(CurrentDate()) Else [Budget AMT]
Formula only fulfills second condition (ELSE) and ignores the first one. For some reason its not recognizing the UserResponse to fulfill the formula.
Kindly assist me. Appreciate - Thank MUCH!!!
6 Answers
-
Nov 09, 2016 at 09:22 AM
what is the value you are getting with =UserResponse(“Prompt Text”) ?
Might be the reason your first part is not working because UserResponse(“Prompt Text”)=FormatDate(CurrentDate();”Mmm yyyy”) condition is not getting the matching records.
It will be true if in userresponse() you will get the values like Mmm yyyy format.
Add comment
-
Nov 09, 2016 at 09:22 AM
Try this:
=If (formatdate(todate(UserResponse(“Prompt Text”);"Mmm yyyy");"Mmm yyyy")=FormatDate(CurrentDate();”Mmm yyyy”)) Then ([Budget AMT]/DayNumberOfMonth(LastDayOfMonth(CurrentDate())))*DayNumberOfMonth(CurrentDate()) Else [Budget AMT]
Add comment
-
Nov 10, 2016 at 04:34 AM
Amit, l believ u nailed it on the head.
When l checked =UserResponse("prompt text") l get the Month and Year selected at prompt (i.e. Oct 2017)
When l checked = UserResponse(“Prompt Text”)=FormatDate(CurrentDate();”Mmm yyyy") l get 0.
I also used Kuldeep formula but not working. Appreciate all the help here!!!
Add comment
-
Nov 10, 2016 at 10:32 AM
The logic do seems to be working, if you are getting 0 for "= UserResponse(“Prompt Text”)=FormatDate(CurrentDate();”Mmm yyyy")". Oct 2017 = Nov 2016 will return 0.
Add comment
-
Nov 10, 2016 at 10:29 AM
in above post you have mentioned OCT 2017,if this is the value you are comparing in the formula then it's not going to be true.Reason is other part of formula is based on the current date.That is Nov 2016.
Run your report for Nov 2016 and check.
Add comment
-
Nov 14, 2016 at 11:38 PM
Hi Amit/Kuldeep, you are very correct. Totally didnt realise this - My bad! Now this brings the need for your help. My user response is Nov 2017 for calendar period Nov 2016 because report runs by fiscal year. Can the UserResponse be used by converting or referencing the calendar date key object in report? User can only select one period at a time and the calendar date key object is in available object
NOTE: For every April, May, June, July, Aug, Sep, Oct Nov Dec of current Yr. The FY is ahead by one. Jan Feb Mar is current Yr but other month is ahead due to fiscal year classification.
Appreciate ur support as always.
Add comment
Add comment