Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

IF Statement taking too long to execute

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())))))))))))
Need Help
  • mwarren's avatar
    mwarren
    7 years ago

    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.

     

  • dedelman_clng's avatar
    dedelman_clng
    7 years ago

    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", 
...

     

     

8 Replies

  • mwarren's avatar
    mwarren
    Frequent Visitor
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        mwarren
        Frequent Visitor
        7 years ago

        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.