Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
*(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
*(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
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 )
Oops. It should be the latter. Good catch.
Alright, thank you very much for your help. So far this seems to be working correctly.