topic Error in calculated dimension that works as expression. in QlikView

Error in calculated dimension that works as expression.

Fri, 30 Mar 2012 13:27:49 GMT

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?

Re: Error in calculated dimension that works as expression.

SunilChauhan — Fri, 30 Mar 2012 13:54:21 GMT

use

aggr(expression,keyfield) in dimension

Error in calculated dimension that works as expression.

jonathandienst — Fri, 30 Mar 2012 14:10:35 GMT

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