Rules for Month allocation with start and finish dates

[BigG]

Hi, will try and keep this simple

I have a cube that users input values, then can also enter start month and end month (no dimension for months in this cube)

This value/12 is allocated to a further cube with an extra month dimension. Also based on start and end month.

Trying to find the best, simplist method for doing this allocation through cube rules. Since the source cube doesnt have months I have difficulty in setting rules without repeating 12 times (one for each month). Any suggestions welcome

eg Jan only example below - would prefer not to repeat

Code: Select all

#COMPARE START AND  END MONTH WITH TARGET AREA MONTH
['Value' ,'Jan'] =N: If (DIMIX( 'Month',(DB('Input cube', !dim1, !dim2, !dim3, 'Start Month')))
< DIMIX( 'Month', 'Jan') %
(DIMIX( 'Month',(DB('Input cube', !dim1, !dim2, !dim3,  'End Month'))))
> DIMIX( 'Month', 'Jan'),
#THEN 1/12 value applied
DB('Input cube', Dim1,DIm2, Dim3, 'Value)/12, 
# ELSE continue to  next  month
CONTINUE);

[lotsaram]

I would advise against using a DIMIX text and use an attribute for "month index" instead. It's more flexible and less prone to breakage should the dimension structure of the month dimension change (such as adding a consolidation, etc.)

Leaving this aside and to answer your question in the most straightforward way, there is no reason to repeat this rule repetitively as you can just use the !dimension notation to return the element name at the cell intersection for which the rule is being evaluated. Hence your rule (with no improving modifications) would be this:

Code: Select all

#COMPARE START AND  END MONTH WITH TARGET AREA MONTH
['Value'] =N: If (DIMIX( 'Month',(DB('Input cube', !dim1, !dim2, !dim3, 'Start Month')))
< DIMIX( 'Month', !Month) %
(DIMIX( 'Month',(DB('Input cube', !dim1, !dim2, !dim3,  'End Month'))))
> DIMIX( 'Month', !Month),
#THEN 1/12 value applied
DB('Input cube', Dim1,DIm2, Dim3, 'Value)/12,
# ELSE continue to  next  month
CONTINUE);

[Steve Vincent]

you may also wish to contemplate using TI rather than rules. Depends on the customer needs but if the data isn't changed all the time then it might be easier to do the transfer by TI instead. It'd certainly be lighter on the server load should you run in to performance issues with the rule.

[BigG]

thanks for reponse.

Going to to TI process as suggested.
Using atttribute flag 1 to 12 instead of dimix for process.

cheers