topic Re: Formula Returns Null. Should return value in QlikView

Formula Returns Null. Should return value

Thu, 25 Apr 2013 13:10:40 GMT

I am having issues with the following formula. It seems to not like part of it and returns null which returns null for the entire formula. In the case of {<trantype={'MFG-CUS','MFG-STK'}>}

there are actualy no records to return which I am guessing is why I am getting a null result instead of a 0 result. Is there anyway to tell that section that if no records are found then to return a 0?

sum({<trantype={'MFG-CUS','MFG-VAR'}>} tranqty *( lbrunitcost)) //Returns 0

(

((//[Labor to Date]

sum({<jobclosed={'Closed'}>}(0 + (laborhrs * laborrate)))

sum({<trantype={'*-MTL','*-DMR','WIP-MFG'},jobclosed={'Closed'}>}(0 + if(wildmatch(trantype,'*-DMR'),(tranqty * lbrunitcost * (-1)),

if(wildmatch(trantype,'ADJ-*'),lbrunitcost, tranqty * lbrunitcost))))

//[Inventory Labor]

sum({<trantype={'*-STK','MFG-WIP'},jobclosed={'Closed'}>}(0 + tranqty * lbrunitcost))

sum({<trantype={'MFG-CUS','MFG-VAR'},jobclosed={'Closed'}>} (0 + tranqty *( lbrunitcost )))) // value returned 1,036.16

*(sum({<trantype={'MFG-CUS'}>}if(jobclosed='Open',0, tranqty)) / (sum({<trantype={'MFG-CUS','MFG-STK'}>}if(jobclosed='Open',0, tranqty ))))) //This returns null causing the entire section to be null. Actual value returned is 0

((

(//[Labor to Date]

(sum({<jobclosed={'Closed'}>}(0 + (laborhrs * laborrate)))

sum({<trantype={'*-MTL','*-DMR','WIP-MFG'},jobclosed={'Closed'}>}(0 + if(wildmatch(trantype,'*-DMR'),(tranqty * lbrunitcost * (-1)),

if(wildmatch(trantype,'ADJ-*'),lbrunitcost, tranqty * lbrunitcost))))

//[Inventory Labor]

sum({<trantype={'*-STK','MFG-WIP'},jobclosed={'Closed'}>}(0 + tranqty * lbrunitcost))

sum({<trantype={'MFG-CUS','MFG-VAR'},jobclosed={'Closed'}>}(0 + tranqty *( lbrunitcost ))))) // This returns 1,036.16

((//[Labor to Date]

sum({<jobclosed={'Closed'}>}(0 + (laborhrs * laborrate)))

sum({<trantype={'*-MTL','*-DMR','WIP-MFG'},jobclosed={'Closed'}>}(0 + if(wildmatch(trantype,'*-DMR'),(tranqty * lbrunitcost * (-1)),

if(wildmatch(trantype,'ADJ-*'),lbrunitcost, tranqty * lbrunitcost))))

//[Inventory Labor]

sum({<trantype={'*-STK','MFG-WIP'},jobclosed={'Closed'}>}(0 + tranqty * lbrunitcost))

sum({<trantype={'MFG-CUS','MFG-VAR'},jobclosed={'Closed'}>}(0 + tranqty *( lbrunitcost )))) // value returned 1,036.16

*(sum({<trantype={'MFG-CUS'}>} if(jobclosed='Open',0, tranqty))/ (sum({<trantype={'MFG-CUS','MFG-STK'}>}if(jobclosed='Open',0, tranqty ))))) //Returns null should return 0

))

)

Final Value returned should be 1,036.16

(0 + (1,036.16 * 0) + (1,036.16 - (1,036.16 * 0 )))= 1,036.16

Re: Formula Returns Null. Should return value

Gysbert_Wassenaar — Thu, 25 Apr 2013 13:35:08 GMT

*(sum({<trantype={'MFG-CUS'}>} if(jobclosed='Open',0, tranqty)) / (sum({<trantype={'MFG-CUS','MFG-STK'}>}if(jobclosed='Open',0, tranqty )) )))

If the bold part returns 0 you're dividing by zero. And that is frowned upon in this universe. You could try to wrap an alt() around the entire division...

* alt((sum({<trantype={'MFG-CUS'}>} if(jobclosed='Open',0, tranqty)) / (sum({<trantype={'MFG-CUS','MFG-STK'}>}if(jobclosed='Open',0, tranqty ))),0 ) ))

... but I'm not sure that would bypass the divide-by-zero issue.

edit: fixed the alt

Re: Formula Returns Null. Should return value

Thu, 25 Apr 2013 13:45:27 GMT

would it be

alt(0,(sum({<trantype={'MFG-CUS'}>} if(jobclosed='Open',0, tranqty)) / (sum({<trantype={'MFG-CUS','MFG-STK'}>}if(jobclosed='Open',0, tranqty ))) )

alt((sum({<trantype={'MFG-CUS'}>} if(jobclosed='Open',0, tranqty)) / (sum({<trantype={'MFG-CUS','MFG-STK'}>}if(jobclosed='Open',0, tranqty ))),0 )

Re: Formula Returns Null. Should return value

Gysbert_Wassenaar — Thu, 25 Apr 2013 13:58:30 GMT

Oops. It should be the latter. Good catch.

Re: Formula Returns Null. Should return value

Thu, 25 Apr 2013 13:59:56 GMT

Alright, thank you very much for your help. So far this seems to be working correctly.