Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Formula Returns Null. Should return value

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

Tags (1)
1 Solution

Accepted Solutions

Re: Formula Returns Null. Should return value

*(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


talk is cheap, supply exceeds demand
4 Replies

Re: Formula Returns Null. Should return value

*(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


talk is cheap, supply exceeds demand
Not applicable

Re: Formula Returns Null. Should return value

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

or

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

Oops. It should be the latter. Good catch.


talk is cheap, supply exceeds demand
Not applicable

Re: Formula Returns Null. Should return value

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

Community Browser