topic Calculation.... in QlikView

Calculation....

tmumaw — Wed, 16 Nov 2016 11:57:52 GMT

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

Re: Calculation....

sunny_talwar — Wed, 16 Nov 2016 12:05:03 GMT

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.

Re: Calculation....

tmumaw — Wed, 16 Nov 2016 12:17:01 GMT

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

Re: Calculation....

tmumaw — Wed, 16 Nov 2016 12:25:30 GMT

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