cancel
Showing results for 
Search instead for 
Did you mean: 

Data Modeling - Creating a multipling factor for a year

chris_robertson
Participant
0 Kudos

We have a need on a report to adjust the result by a percent based on the year. i.e. if the value is 100 and it is in year 2007 we might add 1% making the end result 101. But if the value is 100 and it is in year 2006 we might want to subtract 2% making the end result 98.

My first thought was to create this percent as an attribute on 0CALYEAR, but it is not possible to place attributes on time charateristics. Does anyone have any suggestions for me to consider on how we might model this?

Thanks,

Chris

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello Chris very simple suggestion for you copy from 'value' field 2 new value fields in query one for 2006 and filter this with calyear 2006 and multiply with .98 and one for 2007 and filter with calyear 2007 and multiply with 1.01. Show them in columns.

Don't worry about records for 2006 the 'value 2007' will be 0 and records for 2007 'value 2007' will be 0

Simple one

Sarhan.

chris_robertson
Participant
0 Kudos

Sarhan,

Thanks for your responce. If I understand you correctly this would all be done in the query. I guess that would be an option for us. My preference would be to have the value saved in master data so that I don't have to change the query if the percent changes or when we start to report 2008 data I don't have to create a third new value field.

Thanks,

Chris

Former Member
0 Kudos

I see chris,

then why don't you create an ODS with calyear and factor (%). Join them with Multicube or infoset and then apply my solution by multiplying not with constant by the factor from new ODS filtered by calyear.

Another simple one, I know not perfect but fast to implement

Sarhan.

chris_robertson
Participant
0 Kudos

Thanks. This gives me a couple more options to consider. I'm not sure how the multiprovider would work because of the UNION join. And I've not had the opportunity to work with infoset yet. But hey this could be my opportunity.

Thanks for taking the time to provide me with a few more options to test.

Chris

Former Member
0 Kudos

Happy to hear that Chris.

Sarhan.

Answers (0)