on 04-30-2015 3:51 AM
I need Formula to show previous yr as Year 2014 and Actual yr Year 2015. When I use this formula Userresponse("my prompt name"). It is NOT even showing me the current year Year 2015.
I have my fiscal year set to optional from BEX query panel in the EDIT mode. When I put the fiscal year key into the Query filter panel, YES - I see the year with the formula Userresponse("my prompt name") but then because the year is set to optional from BEX query panel its not showing. What am I missing.
I need formula to show the current year based on the year object and also another formula to show previous year based on the year object on the column header.
Thank you
Shila
Hi Shila,
See you create another provider just as the first one with same result objects or less. In this second provider don't place any prompt. So it pulls data for all the years and first provider pulls data only for that prompt based info. Once you run the queries and onto the report merge year and other date related objects. So now use these merged objects to pull prev yr info. Previous Month= Tonumber(Userresponse("Enter Month:"))-1 and add this logic also(if(Userresponse("Enter Month:")=01) then "12") and for Previous Year=Tonumber(Userresponse("Enter Year:"))-1. Now combine these 2 objects to get month and year combination.
Grtz
-Anila.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Shila,
Use following formula :
current year = "Year" + " "+[Year]
Previous year = "Year" + " "+([Year]-1)
Amit, I am not able to understand why you have substr in your formula above. Could you please explain.
Regards,
Tanisha
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
This is the query.
I need Formula to show previous yr as Year 2014 and Actual yr Year 2015. When I use this formulaUserresponse("my prompt name"). It is NOT even showing me the current year Year 2015.
As per this my understanding from userresponse value will come "Year 2015".If prompt value is "Year 2015" then you can directly minus 1.beacuse variable datatype is string.
Amit
I used this formula
=FormatDate(RelativeDate(ToDate(UserResponse("Enter Fiscal Year/Period");"MMM yyyy");-1);"MMM yyyy")
lets say user select March 2014, the formula should show March 2013 but its giving me Feb 2014.
I need this to show March 2013 (prior year period based on selection)
Thank you.
Hi,
use this.
=FormatDate(RelativeDate(ToDate(UserResponse("Enter Fiscal Year/Period");"MMM yyyy");-12);"MMM yyyy")
other way
create one variable.
V Month=month(ToDate(UserResponse("Enter Fiscal Year/Period");"MMM yyyy"))
V Year=Year(ToDate(Right([Year];4);"yyyy"))-1
Final variable = V Month+" "+ V Year
Amit
Hi,
If you will not enter the fiscal year value in the prompt then userresponse will not capture the fiscal year value.
Either you will make fiscal prompt as mandatory to capture userresponse value.
Other thing you can test if you are pulling fiscal year object in the report and if you are getting 2015,2014,0r some other value then create one variable and use max function to capture fiscal year value.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Amit and Tanisha. I need the formula to see user repsonse and give me previous year and based on your formulas - This is giving me error. For current year I used this =userresponse("prompt object:) and I got current year to show.
How can I get prior year using user selection to derive that. Thanks again - really appreciate all your help.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.