Substitute to nested ifs

[#1]

Hi Everyone

I've been trying to figure this one out for a while and am having no luck. I wish to get rid of my nested if statements in my variables as I have a lot of these

if(IsNull(sum({<Year,Month,Day, F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase))

or

(sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=3,50,

if((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >3

and (sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=4,45,

if((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >4

and (sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=6,40,

if((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >6

and (sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=8,35,

if((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >8

and (sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=10,30,

if((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >10

and (sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=11,29,

if((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >11

and (sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=13,28,

if((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >13

and (sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=16,27,

if((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >16

and (sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=20,26,

If((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >20,25

))))))))))

[giakoum]

This is a really complicated statement. Depending on records, the performance must be really bad. I would suggest that you create flags in the data model (reload script) and use those in your set analysis statements.

[marcus_sommer]

I suggest you to use a pick(match() approach to simplify your expression. However pick(match()) works only with equal values - a greater or less didn't work. Therefore you need a logic to modify your condition in certain intervals or simply as listing. For your case a round-function like ceil() or floor() and a return-listing should be enough:

pick(match(

ceil(

sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100)),

1,2,3,4,5,6,7,8,9,10 .......),

50,50,50,45,45 .....)

With some adjustment this will work - I use it quite often.

- Marcus

[#4]

This seems to be a big expression and not good for Performance. As a step towards performance tuning. You should aggregate most of stuff in Script.-Ram

[jagan]

Hi,

If you are using in a straight table then try like this, create two expressions

Exp1:

=(sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100)

Exp2:

(sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100)

Now in your actual expression try like this

If([Exp1] > 3 AND [Exp2] <5, 45,

'

'

'

and in presentation tab hide Exp1 and Exp2, by this way you can reuse and reduce the size of the expression.

Hope this helps you.

Regards,

Jagan.

[#6]

I am going to try these suggestions and let you guys know. Thanks a lot!