IF Statement taking too long to execute

[Anonymous]

Hi,

I have created a multiple IF statement but the statement is taking too long to execute, is there a way around this?

My measure:

Trend = IF([Count Negative Results] = 6, "Losing",

IF([Count Negative Results] in {3,4,5} && [Average Moving and CY TTM] <= -.6 ,"Losing",

IF([Count Negative Results] in {4,5} && [Average Moving and CY TTM] <= -.15 && [Average Moving and CY TTM] > -.6, "Declining",

IF([Count Negative Results] in {4,5} && [Average Moving and CY TTM] < 0 && [Average Moving and CY TTM] > -.15, "Declining",

IF([Count Negative Results] = 3 && [Average Moving and CY TTM] <= -.15, "Downturn",

IF([Count Negative Results] in {1,2} && [Average Moving and CY TTM] < 0, "Potential Downturn",

IF([Count Negative Results] in {4,5} && [Average Moving and CY TTM] > 0, "Potential Recovery",

IF([Count Negative Results] in {1,2} && [Average Moving and CY TTM] > 0 && [Average Moving and CY TTM] < .15, "Growing",

IF([Count Negative Results] in {1,2,3} && [Average Moving and CY TTM] > .15 && [Average Moving and CY TTM] < .6, "Growing",

IF([Count Negative Results] in {1,2,3} && [Average Moving and CY TTM] > .6, "Winning",

IF([Count Negative Results] = 0, "Winning",

BLANK())))))))))))

[mwarren · Accepted solution]

Have you tried using 'SWITCH'?

Trend = SWITCH( TRUE() ,
[count negative results] = 6 , "Losing" ,
[count negative results] in {3,4,5} && [Average Moving and CY TTM] <= -.6 , "Losing" , 

etc....

This has sped up my nested IF statements in the past.

[mwarren · Accepted solution]

Have you tried using 'SWITCH'?

Trend = SWITCH( TRUE() ,
[count negative results] = 6 , "Losing" ,
[count negative results] in {3,4,5} && [Average Moving and CY TTM] <= -.6 , "Losing" , 

etc....

This has sped up my nested IF statements in the past.

[Anonymous]

Thanks for the reply,
It worked faster than IF Statement, however, it still takes around 5 minutes to execute, is there any other workaround?

Thanks again!

[mwarren]

Next thing i would think of is looking at the measures you are referencing in statement and see if there is a way you could run those more efficiently.  

Also maybe VAR them in the function instead of referencing them? I definately think 5 minutes is too long to run that statement though.

[Anonymous]

Hey,

Thanks for the above inputs, I have different measures and i am counting the frequency of negative values of those measure. I dont find any other way to add these measures, please help!

Count Negative Results = IF([TTM Var 1] < 0,1,0) + IF([TTM Var 2] < 0,1,0) + IF([TTM Var 3] < 0,1,0) + IF([TTM Var 4] < 0,1,0) + IF([TTM Var 5] < 0,1,0) + IF([TTM Var 6] < 0,1,0)

Using If statement is the problem here for large computational time, please guide me if there is a way around.

Thanks!

[dedelman_clng · Accepted solution]

Have you tried making a call the measures and storing as variables outside of the IF/SWITCH ?

Trend = 
var __CountNeg = [Count Negative Results]
var __AvgMov = [Average Moving and CY TTM]

RETURN

SWITCH(TRUE(),
__CountNeg = 6, "Losing", 
__CountNeg in {3,4,5} && __AvgMov <= -.6 ,"Losing", 
...

[Anonymous]

Worked like a charm! Thank you all!

[TomMartens]

Hey @Anonymous,

please mark the most helpful post as an answer, as it also helps others in this forum.

Regards,

Tom

[mwarren]

Instead of a measure try doing that for a calculated column. This way it will do the calculation when it brings in the data rather than at the time you are trying to run the query.