Using String for rules

[appleglaze28]

Hi

I'd like to ask how do you write a rule in a cube...For example I have a Assumption Cube where Airfare & Per Diem are defined per location.

I have another cube, Travel Cube where I have a string element named Training Site and a simple element named Per Diem,Number of Training Days & Number of Employee.

So I wrote a calculation like
['Per Diem'] = IF (['Training Site']@='Singapore',((DB(Assumption Cube, !Year, 'Singapore','Daily Training Per Diem','USD']*['Number of Employee'])*['Number of Training Days']),continue);

If I write it as such I come up with Incorrect Logical Comparison. But if I put a "@" before the "=" sign. I get "Syntax Error on or before ['Training Site']@=' logical expression."

[Gregor Koch]

Hi

If you do a String comparison in a rule you need to use the DB() syntax rather than [] for the cell reference.

So your rule should read somehting like this:

['Per Diem'] = IF (DB('Travel Cube', !dim1, !dim2,'Training Site')@='Singapore',((DB(Assumption Cube, !Year, 'Singapore','Daily Training Per Diem','USD']*['Number of Employee'])*['Number of Training Days']),continue);

Cheers

[appleglaze28]

Can you give me on tips with regards to using a string element in a dimension to be used as a dimension in another cube? Although I'm not getting error I'm not sure how its suppose to be written cause I don't see values being transferred to another cube.

I have a Travel Cube that whose Representation Expense value I want to transfer to a Expense cube.

In the Travel Cube I have the following dimension
(Travel Cube',!Company,!Month,!Year,!Version,'Total Product By Current Type','Representation and Entertainment');

In the Expense Cube I have
('Profit_Center_Overhead',!Company,!Version,!Year,!Month,!Profit_Center,!OPEX_Measures)

How can I get the profit center string element in the travel cube to be able to transfer the data to the expense cube on the right profit center?

[mattgoff]

Are you feeding and have you included FEEDSTRINGS?

[Gregor Koch]

Hi

I guess I am stating the obvious but you need to identify which Profit Centre receives which Travel Expenses. If you have a one to one relationship between company and profitcentre this is realtively easy. But if you have some expenses which should go to profitcentre 1 and some to profitcentre 2 for the same company you have a problem because with the information you gave here there is now way of doing this.

If the expenses in travel cube for one company go to the same company (or another, it's just an additional mapping) and one specific profitcentre in the expense cube this could be your solution.

Create an Attribute (or an 'Info' cube) against both the Company (for the Feeder, not always absolutely necessary but let's go with it for simplicity) and the Profit Centre (for the rule).

If you have an attribute in the Profit Centre dimension called 'Mapped Company' and an attribute in the Company dimension 'Mapped PC' your rule in the Expense cube could be

['Representation']=N:
IF(ATTRS('Profit Centre', !Profit Centre, 'Mapped Company')@=!Company,
DB('Travel Cube',!Company,!Month,!Year,!Version,'Total Product By Current Type','Representation and Entertainment'),STET);


and the Travel cube Feeder statement could be

['Total Product By Current Type','Representation and Entertainment']=>
DB ('Profit_Center_Overhead',!Company,!Version,!Year,!Month,ATTRS('Company', !Company, 'Mapped PC'),'Representation');


On the side: If you don't calculate a String don't feed it and only use Feedstrings if necessary. In this case you don't have to.

Cheers

[appleglaze28]

I think the feeder you gave me is applicable only on a 1:1 basis but in my case it be like I have 100 product brand of which 20 product's cost shall be allocated to a specific profit center so on and so forth.

Although the products have been grouped under the Product dimension to their specific profit center. I'm not getting any value being fed the exact profit center element on another cube. Is there something wrong with the FEEDSTRING I used.

Although this is the only way I know how to feed the data is there a even more dynamic way to approach this kind of data transfer from 1 cube to another

TARGET CUBE RULES

Code: Select all

['Airfare']=IF (DB('Product_Gross_Margin',!Company,!Month,!Year,!Version,!Products,'Training Site')@='Singapore',(DB('Travel_Assumptions','Singapore',!Year,'PHP','Airfare Cost')*['Number of Trainee']),continue);
['Airfare']=IF (DB('Product_Gross_Margin',!Company,!Month,!Year,!Version,!Products,'Training Site')@='Indonesia',(DB('Travel_Assumptions','Indonesia',!Year,'PHP','Airfare Cost')*['Number of Trainee']),continue);
['Airfare']=IF (DB('Product_Gross_Margin',!Company,!Month,!Year,!Version,!Products,'Training Site')@='Malaysia',(DB('Travel_Assumptions','Malaysia',!Year,'PHP','Airfare Cost')*['Number of Trainee']),continue);
['Airfare']=IF (DB('Product_Gross_Margin',!Company,!Month,!Year,!Version,!Products,'Training Site')@='Thailand',(DB('Travel_Assumptions','Thailand',!Year,'PHP','Airfare Cost')*['Number of Trainee']),continue);
['Airfare']=IF (DB('Product_Gross_Margin',!Company,!Month,!Year,!Version,!Products,'Training Site')@='USA',(DB('Travel_Assumptions','USA',!Year,'PHP','Airfare Cost')*['Number of Trainee']),STET);

How do I write the feeders for ths since I'm not too familiar with the FEEDSTRINGS. Do I put it before the SKIPCHECK or after? What are the items to be fed for FEEDSTRING & FEEDERS? Or you can only either Feed to 1 type of variable either String or not. I cant see to find a good book to help me get a better understanding of Feedstrings.

[Steve Rowe]

Hi Apple,

Just two very quick points.

1. You don't need feed strings to get the rules you have written to work, you are not writing a string rule.
2. IMO You should have Training Site as a dimension in Product_Gross_Margin rather than a piece of data, then alot of your problems should go away.

That said the feeder for all your rules should be
['Number of Trainee']=>['Airfare'];

Your set of If statements can be simplified to 1 rule if the training site dimension is in the cube you are writing the rules for

['Airfare']=N:IF (DB('Product_Gross_Margin',!Company,!Month,!Year,!Version,!Products,'Training Site')@=!TrainingSiteDimension,(DB('Travel_Assumptions',!TrainingSiteDimension,!Year,'PHP','Airfare Cost')*['Number of Trainee']),continue);

Also in genral terms numeric rules should always be written with the N qualifier so that they only apply at the N level.

If you had Training SIte as a dimension in all the cubes then your rule would just be

['Airfare']=N:DB('Travel_Assumptions',!TrainingSiteDimension,!Year,'PHP','Airfare Cost')*['Number of Trainee');

It's difficult to help much more without full detail on the dimensionality of the cubes involved.

Cheers, HTH!

[appleglaze28]

Code: Select all

['Airfare']=N:IF (DB('Product_Gross_Margin',!Company,!Month,!Year,!Version,!Products,'Training Site')@=!TrainingSiteDimension,(DB('Travel_Assumptions',!TrainingSiteDimension,!Year,'PHP','Airfare Cost')*['Number of Trainee']),continue);

Correct me if I'm wrong, base on this one that you gave me, can I really use the dimension as part of my IF statement? I'm not sure if that's clear though. Cause if so that would mean, I don't need to add rules if I have additional locations in my assumptions, it would just apply for all.

[Steve Rowe]

Hi appleG,

The rule should work and you're correct if you add new locations the rule will not need updating, though it's possible you may need to resave the rules to fire feeders.

Can't you get it to work?

Cheers

[appleglaze28]

Code: Select all

['Airfare']=N:IF (DB('Product_Gross_Margin',!Company,!Month,!Year,!Version,!Products,'Training Site')@=!TrainingSiteDimension,(DB('Travel_Assumptions',!TrainingSiteDimension,!Year,'PHP','Airfare Cost')*['Number of Trainee']),continue);

I tried testing the TI script you gave me and its giving me an error:

So I can only use precise statements in script like the one I did before.

Code: Select all

#REGION AIRFARE COMPUTATION
['Airfare'] = N: IF (DB('plan_product_gross_margin_cost', !base_company, !base_month, !base_year, !base_product, !base_version, 'Training Site')@='Singapore',(DB('assumption_travel_cost', 'Indonesia', !base_month, !base_year, 'PHP','Airfare Cost')*['Numbe
r of Trainee']),continue);
['Airfare'] = N: IF (DB('plan_product_gross_margin_cost', !base_company, !base_month, !base_year, !base_product, !base_version, 'Training Site')@='Malaysia',(DB('assumption_travel_cost', 'Indonesia', !base_month, !base_year, 'PHP','Airfare Cost')*['Number
 of Trainee']),continue);
['Airfare'] = N: IF (DB('plan_product_gross_margin_cost', !base_company, !base_month, !base_year, !base_product, !base_version, 'Training Site')@='Thailand',(DB('assumption_travel_cost', 'Indonesia', !base_month, !base_year, 'PHP','Airfare Cost')*['Number
 of Trainee']),continue);
['Airfare'] = N: IF (DB('plan_product_gross_margin_cost', !base_company, !base_month, !base_year, !base_product, !base_version, 'Training Site')@='Indonesia',(DB('assumption_travel_cost', 'Indonesia', !base_month, !base_year, 'PHP','Airfare Cost')*['Numbe
r of Trainee']),continue);
['Airfare'] = N: IF (DB('plan_product_gross_margin_cost', !base_company, !base_month, !base_year, !base_product, !base_version, 'Training Site')@='USA',(DB('assumption_travel_cost', 'Indonesia', !base_month, !base_year, 'PHP','Airfare Cost')*['Number of T
rainee']),STET);

[lotsaram]

Your rule is not a very efficient way of tackling this problem (either from a calculation point of view and especially from a maintenance and writing the calculation point of view.)

This type of calculation is much better done in a lookup calculation cube where you have a matrix of from and to destinations and KM or cost for each leg.
. . . it looks like this is what you were trying to do before you reverted back to the old method.

If you split the calculation by destination/leg in the cube then you should be able to avoid the If statement alltogether (but the construct "IF( !dimension @= ..." is valid and a very common test in rules ...)