Skip to Content
0

Converting numeric posting period into Month name

Nov 22, 2016 at 04:28 AM

125

avatar image

Hello,

I am stuck in creating a variable.

I have a Numeric field called "Actual Period" which is driving through BEx text variable into webi report therefore when I change the posting period filter before I run teh report it shows the period in the column header For Example if I run the report for posting period 010 then it shows me "Actual Period 010" in the column header but my requirement is to show the posting period s month name such as for 010 = October and 011 = November.

I created a variable called (Posting Period Name) with the following values so whenever user run the report with particular posting period it should show the month name rather than month number.

=If([Posting period]) = "001" Then "January"

=If([Posting period]) = "002" Then "February"

=If([Posting period]) = "003" Then "March"

=If([Posting period]) = "004" Then "April"

=If([Posting period]) = "005" Then "May"

=If([Posting period]) = "006" Then "June"

=If([Posting period]) = "007" Then "July"

=If([Posting period]) = "008" Then "August"

=If([Posting period]) = "009" Then "September"

=If([Posting period]) = "010" Then "October"

=If([Posting period]) = "011" Then "November"

=If([Posting period]) = "012" Then "December"

But I am not getting the desired result, I want my column header to show "Posting Period October" when I run the report for period 010.

Can someone please advise whats wrong with my formula and how do I fix it to get the desired result.

Will be much appreciated.

Thanks and Regards

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

2 Answers

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

You have to create nested if-else condition but you have created individual if-else condition.

it should be like..

Posting Period Name=If([Posting period]) = "001" Then "January" elseIf([Posting period]) = "002" Then "February" elseIf([Posting period]) = "003" Then "March" elseIf([Posting period]) = "004" Then "April" elseIf([Posting period]) = "005" Then "May" elseIf([Posting period]) = "006" Then "June" elseIf([Posting period]) = "007" Then "July" elseIf([Posting period]) = "008" Then "August" elseIf([Posting period]) = "009" Then "September" elseIf([Posting period]) = "010" Then "October" elseIf([Posting period]) = "011" Then "November" elseif([Posting period]) = "012" Then "December"

other option to use below formula to get full name..no need of if-else condition.

=FormatDate(ToDate(Substr([Posting period];2;2);"MM");"Mmmm")

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Amit

Both the formulas you gave me are coming like this, pls see the screenshot. I want month name in the column header. Thanks

capture1.jpg (15.1 kB)
0

I did not understand your requirement.are you getting one value or multiple values in the Posting period object in webi?

if you want to display all month name in headers then use crosstab to display all values

0

Amit,

Lets say if my Key Figure is called "Actual Period" and when I run the report based on posting period 010 then on the Key Figure Header it says "Actual Period 010" BUT I want want is that header of that Key Figure should says "Actual Period October" instead of "Actual Period 010"

All I want is the month name in the header (blue header) i.e October for posting period 010 and same when i use a different posting period such as 011 should be equal to month Novmber.

Please see the screenshot, thanks

capture1.jpg (12.3 kB)
0
AMIT KUMAR
Nov 22, 2016 at 04:35 PM
0

follow these steps.

V Month Name=FormatDate(ToDate(Substr([Posting period];2;2);"MM");"Mmmm")

Put this on header.

=NameOf([Posting period]) +" "+[V Month Name]


untitled.png (6.5 kB)
Show 2 Share
10 |10000 characters needed characters left characters exceeded

I created a variable : V Month Name=FormatDate(ToDate(Substr([Posting period];2;2);"MM");"Mmmm")

Then I used it in the formula

This =NameOf([Posting period]) +" "+[V Month Name] is giving me #Multivalue error

Please advise, will appreciate

Thanks

capture1.jpg (15.4 kB)
1

This is happening because you have multiple values in the Posting period object.if you need to display all months then use crosstab

0