Calculation....

[tmumaw]

Morning everyone....

Need a little help with a sum statement.  I need to first check to see if the plan dollars are less than zero and do one calculation otherwise it's greater than zero and do another.  Here is what I have but not working out too well.

Thanks

if(Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) < 0,
      if((Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) -
          Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[GL Hier 1] = {[ZMR Chart of Accounts]}, YTDFlag = {1}>} [In company code currency_HSL] * -1)) /
          Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) > 0,'qmem://<bundled>/BuiltIn/arrow_n_g.png'),
     if((Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) -
         Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[GL Hier 1] = {[ZMR Chart of Accounts]}, YTDFlag = {1}>} [In company code currency_HSL] * -1) /
         Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) < 0,'qmem://<bundled>/BuiltIn/arrow_s_r.png', 
If(sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[GL Hier 1] = {[ZMR Chart of Accounts]}, YTDFlag = {1}>} [In company code currency_HSL] * -1) /
        Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) > 0,'qmem://<bundled>/BuiltIn/arrow_n_g.png',
If(sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[GL Hier 1] = {[ZMR Chart of Accounts]}, YTDFlag = {1}>} [In company code currency_HSL] * -1) /
        Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) < 0,'qmem://<bundled>/BuiltIn/arrow_s_r.png','qmem://<bundled>/BuiltIn/minus.png'))))) 

[sunny_talwar]

There are few things

1) You are multiplying by -1 and you are subtracting the expression. That seems like addition to me? Do you want to subtract or add? I think it should be subtract, right?

2) It seems you are missing a parenthesis here?

There might be other issues such as misplacement of parenthesis etc, but those are difficult to look for in a complex expression such as this.

[tmumaw]

Those are fine.  I think it has to do with the location of the ')'

[tmumaw]

Got it......

if(Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) < 0,
       if((Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) -
          Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[GL Hier 1] = {[ZMR Chart of Accounts]}, YTDFlag = {1}>} [In company code currency_HSL] * -1)) /
Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) > 0,'qmem://<bundled>/BuiltIn/arrow_n_g.png','qmem://<bundled>/BuiltIn/arrow_s_r.png'),
If(sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[GL Hier 1] = {[ZMR Chart of Accounts]}, YTDFlag = {1}>} [In company code currency_HSL] * -1) /
Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) > 0,'qmem://<bundled>/BuiltIn/arrow_n_g.png',
If(sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[GL Hier 1] = {[ZMR Chart of Accounts]}, YTDFlag = {1}>} [In company code currency_HSL] * -1) /
Sum({<FISYR = {$(=$(vFisYr) - 0)},YTDFlag = {1},[Plan Description] = {'OP Plan'}>} Plan_Dollars) < 0,'qmem://<bundled>/BuiltIn/arrow_s_r.png','qmem://<bundled>/BuiltIn/minus.png')))