Previous Year Description/Formula

[Former Member]

Hi folks,

I use BOXI r2 sp 4. If I use the following variable to retrieve the current year; how do I modify it to return the previous year and the next year? Also, if someone could explain the logic to their solution, I would greatly appreciate it.

Type = character

Format = yyyy-MM

>=Substr([Billing Year Month];1;4)

Thanks!

W.

[Former Member · Accepted solution]

Hi Wanetta,

Previous Year

=FormatNumber((ToNumber(Substr([Billing Year Month];1;4))-1);"0000")

To explain

If the billing Year Month is 2008-01. Your substr function will return 2008. The ToNumber function returns the year as 2008 as a number. You then take a year off. Finally the FormatNumber will convert it back to character type.

Next Year

=FormatNumber((ToNumber(Substr([Billing Year Month];1;4))+1);"0000")

Hope this helps

Alan

[Former Member · Accepted solution]

Hi Alan,

Well, the formulas worked fabulously....just a wee bit of a problem. serious understatement

I tried to use the formulas in variables for Previous Year and Next Year calculations. I assumed that if I added the variables to my header in the crosstab, that when I pulled the universe measure "Cost of Service" or "Billing Units" that it would automatically calculate itself and return the correct value.

This assumption is clearly wrong and since I do not understand why, I do not know how to begin finding the correct solution. So I'm going back to the beginning and will start with the basic report requirements.

Clearly, I need to RETHINK the problem...and obviously, I could use some help. Do you have any suggestions about how I should modify my views for the following requirements?

REQUIREMENTS:

1 year prompt (between) Billing Year Month or Billing Quarter

prompt on Business Segment

Report should include Previous and Next Year aggregates.

****************************************************

I'm adding some formulas that I've been trying to utilize (based upon your previous suggestions as well as a few others) in the report as other solutions but have been unable to correctly write/execute them.

>=Cost of Service Where(Billing Year Month=If(ToNumber(SubStr(UserResponse("Enter Billing Year Month Start:" );6,2))=1;(SubStr(UserResponse("Enter Billing Year Month Start:" );1,4)"-12";(FormatNumber(ToNumber(SubStr(UserResponse("Enter Billing Year Month Start:" );1,4))1;"######"))"-"(FormatNumber(ToNumber(SubStr(UserResponse("Enter Billing Year Month Start:" );6,2));"00")))

>=FormatNumber(Year(ToDate(UserResponse("Enter Billing Year Month Start:");"yyyy-MM"));"######")"-"FormatNumber(MonthNumberOfYear(ToDate(UserResponse("Enter Billing Year Month Start:");"yyyy-MM"));"00")

>=[Cost of Service] Where([Billing Year Month] =UserResponse(u201CEnter Billing Year Month End:u201D)If(MonthNumberOfYear(Billing Year Month())=1;(FormatNumber(Year(Billing Year Month());"######"))"-12";(FormatNumber(Year(Billing Year Month())1;"######"))"-"(FormatNumber(MonthNumberOfYear(Billing Year Month());"00")))

**************************************************************

Your explain on how to write the formulas for previous year and next year were great. Thank you. I have two more questions if you don't mind.

a) What's the logic for the order of a formula?

b) What's the best practice for consistently utilizing the parenthesis in a formula? I find myself consistently mutilating formulas because I do not clearly under the premise for using them.

Thanks!

[Former Member · Accepted solution]

Hi Wannetta,

Now I'm not sure about all the capabilities of the 'where' function in Webi (it just don't exists in version 6.5, which I'm using), but if it has the same functionality as in deski then I would suggest the following solution.

Create variables for calculating the previous, current and next year (just as described by Alan).

Next to that create 3 extra variables like:

check_previous_year

=If [your_object_year] = [variable_previous_year] then 1 else 0

Create the 2 other 'check' variables based on the variables created for current and next year.

Now you can replace the formula in the where statement by the check variables

Cost of Service Where (check_previous_year = 1)

This is the way I make the years variable within my where functions in deski.

Hope this helps you a bit further.

Cheers,

Harry

[Former Member · Accepted solution]

Hi Wanetta,

Sorry for the delay in getting back to you. I have not been able to get to the forum much of late and even when I do get on it seems to be down as much as up.

Anyway, to answer your questions

The order of the formula is generally left to right, using the standard. You cna influence the calculation using the paranthesis

so ab/cd is different from (ab)/(cd). As a result I always use the parenthesis so I can dictate the parts that happen first.

On your question, I think Harry's solution will work. If nothing else it would make the code easier to read. My only question is, are you sure that you have returned all 3 years worth of data from the query. If your query is prompting for a year, the likelihood is that it will restrict the data to just that year. That could be why you are getting the blanks for the other years. If this is the case you will need to make a change at the universe level.

Regards

Alan