Complex IF - SUM - IF scenario - Is it possible?

[#1]

Hello Friends,

I have a requirement to create an "expression dimension" with a similar criteria shown below.

if( sum( if(criteria1, AMOUNT,0)) <= VAL ,'TEXT 1' if(sum( if(criteria2, AMOUNT,0)) > VAL, 'TEXT 2','OTHER'))

ex.

if (

sum( if (Order_YR = 2010 and PRODUCT = DVD , SALES_AMT,0))<= 100 , 'DVD_100_SALES',

if (sum( if (Order_YR = 2010 and PRODUCT = DVD ,SALES_AMT,0)) > 100 , 'DVD_100+_SALES','Other')

)

I tried doing this and gets the "//Error in calculateddimension" error message.

Any idea whether this kind of statement is supported ?

Thanks,

Aji Paul.

[#2 · Accepted solution]

It wont be supported as the dimension is not defined in your expression. I recommened you use aggr() function and define dimensionality for evaluation.

You need not have the if claused in sum, replace them with set analysis for faster computation:

example:

sum( if (Order_YR = 2010 and PRODUCT = DVD , SALES_AMT,0)) can be written as

sum({<Order_YR = {2010} and PRODUCT = {'DVD'}>} SALES_AMT)

Kiran.