Skip to Content
0

USER RESPONSE WITH PRO_RATE FUNCTION - HELP

Nov 09, 2016 at 05:06 AM

49

avatar image

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!!!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

AMIT KUMAR
Nov 09, 2016 at 09:22 AM
0

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.


Share
10 |10000 characters needed characters left characters exceeded
Kuldeep Ghosh Nov 09, 2016 at 09:22 AM
0

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]

Share
10 |10000 characters needed characters left characters exceeded
Shila Brown Nov 10, 2016 at 04:34 AM
0

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!!!

Share
10 |10000 characters needed characters left characters exceeded
Kuldeep Ghosh Nov 10, 2016 at 10:32 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
AMIT KUMAR
Nov 10, 2016 at 10:29 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Shila Brown Nov 14, 2016 at 11:38 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded