Announcements
cancel
Showing results for
Did you mean:
Specialist II

## Calculation....

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')))))

3 Replies
MVP

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.

Specialist II
Author

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

Specialist II
Author

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')))

Community Browser