Multiple conditions in SET ANALYSIS

[#1]

I have a following IF statement

if( days_affected >30  and Date2 < '12/31/2009', (Sum(Sales)*100), if(days_affected >30  and Date2 > '12/31/2009', (Sum(Sales)*2), Sum(Sales)  )).

Now i want to implement this using SET ANALYSIS. Is it possible? If so please reply me ASAP.

[#2 · Accepted solution]

Siva,

You could try something like below.

(Sum({<days_affected = {">30"},Date2 = {"<$(=Date(Date#('12/31/2009','MM/DD/YYYY'),'MM/DD/YYYY'))"}>} Sales)*100)

+

(Sum({<days_affected = {">30"},Date2 = {">$(=Date(Date#('12/31/2009','MM/DD/YYYY'),'MM/DD/YYYY'))"}>} Sales)*2)

+

if((days_affected >30  and Date2 < '12/31/2009') or (days_affected >30  and Date2 > '12/31/2009'),0,Sum(Sales))

Above expression is an untested code. Make sure your data formats are correct with Date2 field.

Hope this helps you.

- Sridhar

[#3]

Hi Sridhar,

Thanks and it is exactly what i want.

Siva

[#4]

what if in the set analysis, i need to do text and formula concatenation?

for example, i have database value like "M-1", "M-2" ..etc..

I tried the following but it wont work.

basically, i hardcoded M-, then the digit i want to compute it. but this wont work in set. problem at the & section

{'M-' & $(='MaxString({$<FLAG={'ACTUAL'}>} SubField(PROD_PLAN_FLAG_REF,'-',2))') }

help please....