Error in calculated dimension that works as expression.

[#1]

I have an expression that works just fine, but when I use it as a calculated dimension, it doesn't.  The expression gives me a margin % and is pasted below.

if([Division Abbreviation]='EFD',

(sum({$ <  [Fiscal Year] = {$(=ONLY("Current Fiscal Year"))}

             >} FACT_SHIPMENTS_AMOUNT_TY)

             -

             sum({$ <  [Fiscal Year] = {$(=ONLY("Current Fiscal Year"))}

             >} FACT_SHIPMENTS_QUANTITY_TY*[Item Cost]*[Item Length]*[Item Width]))

             /

sum({$ <  [Fiscal Year] = {$(=ONLY("Current Fiscal Year"))}

             >} FACT_SHIPMENTS_QUANTITY_TY*[Item Cost]*[Item Length]*[Item Width]),

             (sum({$ <  [Fiscal Year] = {$(=ONLY("Current Fiscal Year"))}

             >} FACT_SHIPMENTS_AMOUNT_TY)

             -

             sum({$ <  [Fiscal Year] = {$(=ONLY("Current Fiscal Year"))}

              >} FACT_SHIPMENTS_QUANTITY_TY*[Item Cost]))

             /

sum({$ <  [Fiscal Year] = {$(=ONLY("Current Fiscal Year"))}

               >} FACT_SHIPMENTS_QUANTITY_TY*[Item Cost])

    )

Any suggestions?

[SunilChauhan]

use

aggr(expression,keyfield) in dimension

[jonathandienst]

Brandon

A calculated dimension needs to return an array of data using something like the aggr function.

But beware that calcuated dimensions can be big performance killers, especially if they are as complex as your expression appears to be. It is better to calculate the dimension values in the load script and use the results in a dimension, if possible. You may need to undestand how selections would affect the dimension and ensure that you keep anough detail to maintain the operation of the system.

Regards

Jonathan