cancel
Showing results for 
Search instead for 
Did you mean: 

Show Year 2014, Year 2015 in my column head - HELP

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member211235
Active Participant
0 Kudos

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.

tanisha_gupta20
Participant
0 Kudos

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

amitrathi239
Active Contributor
0 Kudos

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

tanisha_gupta20
Participant
0 Kudos

Make sense in that case. I was not sure if the those statements could mean the output of year object is Year 2015 especially because it is rare to get an output for Year object as 'Year 2015'. It is usually numeric in nature.

Regards

Tanisha



Former Member
0 Kudos

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.

amitrathi239
Active Contributor
0 Kudos

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


amitrathi239
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

Thanks Amit, I made it mandatory so it can capture user response. So what do I need to do to see the Current fiscal year and Previous Fiscal Year?

All Help Appreciated. Thank you!

amitrathi239
Active Contributor
0 Kudos

Use this formula to capture pervious yr value based on the user entered value in the prompt.

[Year] variable is used to get the user entered prompt value.

="Year"+" "+(ToNumber(Substr([Year];6;10))-1)

Former Member
0 Kudos

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.