Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
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
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?Capture.PNG

2) It seems you are missing a parenthesis here?

Capture.PNG

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
Specialist II
Specialist II
Author

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

tmumaw
Specialist II
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')))