aggr() condition in set analysis

[dialog_sistemi]

Hi

I am using aggr() function to calculate a condition a define a dimension:

if(aggr (avg (COMPLETE), CDC_COMPL)=1,'ok','not ok')

Now I need to sum(SALES) only for the CDC where the condition above is ok.

for example I tried:

sum({<CDC=(if(aggr (avg (COMPLETE), CDC_COMPL)=1,CDC_COMPL))>} SALES)

however the sintax is returning an error,

Can anyone suggest how this can be done?

Thanks,

Marco

[jwjackso]

I think it should be this: 

sum({<CDC=(if(aggr (avg (COMPLETE), CDC_COMPL)=1,CDC_COMPL,0))>} SALES)

I added the ",0" to complete the If statement.  If the IF test is 'not ok', it will sum zero.

[Digvijay_Singh · Accepted solution]

May be this -

sum({<CDC={"=avg(aggr (avg (COMPLETE), CDC_COMPL))=1"}>} SALES)

[dialog_sistemi]

thanks Digvijay Singh. it works great

can I ask you when and why I need to use double quotes? and why you put an equal sign before the statement inside the quotes?

[Digvijay_Singh]

Glad to hear that it worked for you. Set Analysis broadly has 3-4 different formats which are being utilized based on the kind of filtering we need.  Double quote with equal sign is used to write search string inside the element list(statement inside {}).

When we need to write condition in the element list like {X<=0} and the functions like aggr/sum/max etc then it doesn't need to be enclosed with $ sign. You may like to see this attached doc for different formats in different situations.