Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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

View solution in original post

4 Replies
Gysbert_Wassenaar

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

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 )

Gysbert_Wassenaar

Oops. It should be the latter. Good catch.


talk is cheap, supply exceeds demand
Not applicable
Author

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