C level rule to calculate average

[tosca1978]

Hi all,

I have the following rule to caluclate the average cost per day:

Code: Select all

['Product a','Average Final Value'] = C:

IF(
     ATTRS('Time_Day',!Time_Day,'Act For')@='Actual',
     DB('Production Actuals',!Production Hierarchy,'Oil','mmscfd',!Time_Day,'Final Value')\31,
     CONTINUE
    )
;

The results are correct as per the rule so it works. However the days per month needs to be dynamic rather than a hard coded "31". The Time_Day dimension has the days at n level and months at c level. The dimension has an attribute called "Days per month". At the C/Month level of the dimension this attribute is populated with the days of the month.

When I replace the "31" with either of the below 2 pieces of code that try to bring back the "Days per month" just brings back "0":

Code: Select all

ATTRN('Time_Day',!Time_Day,'Days per month'),

or

Code: Select all

DB('}ElementAttributes_Time_Day',!Time_Day,'Days per month'),

Is this because an attribute against a C level element cannot be referenced?

Thanks for any advice.

[Duncan P]

No it's not. There must be some other reason.

A numeric attribute is held in the attribute cube as if it were a string. You can try using it in a rule in the attribute cube to check this. This means that a numeric attribute value can be held against a consolidated item of the corresponding dimension.

Can you confirm that "Days per month" is actually a numeric attribute and not the default string type?

[asutcliffe]

Is "Days per month" defined as a numeric or a string attribute? If it's numeric, both approaches should work provided the attribute is set for the element of "Time_Day" that the rule is applied to whether or not that's a leaf or consolidation.

edit: DuncanP beat me to the punch.

[tosca1978]

No it's not. There must be some other reason.

A numeric attribute is held in the attribute cube as if it were a string. You can try using it in a rule in the attribute cube to check this. This means that a numeric attribute value can be held against a consolidated item of the corresponding dimension.

Can you confirm that "Days per month" is actually a numeric attribute and not the default string type?

Hi Duncan P /asutcliffe , thanks for your replies.

You are correct the Days per month was set up as a sring attribute not numeric! I thought I had checked this becuase when I open the }ElementAttributes_Time_Day dimension in subset editor it is displayed with a # sign next to it rather than the "abc" sybol. However, when I opened the attributes editor it is obvious it was a text attribute.

I have changed this to numberic ans the rule now works.

Many thanks for your help both of you.