Announcements
cancel
Showing results for
Did you mean:
Contributor III

## cannot get the correct count

I have a table like below.

Fields Total Quantity expression : Sum({\$<Quantity = - {'0'}>}Quantity)

• The above expression is meant to only sum when value under Quantity is not equal to 0

Field Original Quantity : Sum([Original Quanity])

Field Short Ship QTY expression : Sum({\$<Quantity = - {'0'}>} Quantity-[Original Quanity])

• The above expression is meant to get the number of quantity - original quantity, however it should not perform the calculation if quantity = 0

Field Short Ship PO COUNT expression:

if(
( Sum({\$<Quantity = - {'0'}>}Quantity) ) -
( Sum({\$<Quantity = - {'0'}>}[Original Quanity]) ) < 0 ,
(Count (distinct [PO Number])),0)

• The above expression is to perform PO distinct count if the short ship qty  is < 0

With the above expression, i am able to get result like below table

 Vendor Name Total Quantity Original Quantity Short Ship QTY Short Ship PO Count PO Number ABC Vendor 0 6,312 0 0 10001 ABC Vendor 3,797 3,798 -1 1 10002 ABC Vendor 0 2,502 0 0 10003 ABC Vendor 0 1,488 0 0 10004 ABC Vendor 1,299 1,302 -3 1 10005 ABC Vendor 0 1,002 0 0 10006 ABC Vendor 990 990 0 0 10007 ABC Vendor 0 900 0 0 10008 ABC Vendor 780 780 0 0 10009 ABC Vendor 0 648 0 0 10010 ABC Vendor 0 600 0 0 10011 ABC Vendor 0 552 0 0 10012 ABC Vendor 366 366 0 0 10013 ABC Vendor 336 336 0 0 10014 ABC Vendor 336 336 0 0 10015 ABC Vendor 329 330 -1 1 10016 ABC Vendor 324 324 0 0 10017 ABC Vendor 300 300 0 0 10018 ABC Vendor 247 246 1 0 10019 ABC Vendor 0 216 0 0 10020 ABC Vendor 168 168 0 0 10021 ABC Vendor 162 162 0 0 10022 ABC Vendor 162 162 0 0 10023 ABC Vendor 156 156 0 0 10024 ABC Vendor 138 138 0 0 10025 ABC Vendor 132 132 0 0 10026 ABC Vendor 108 108 0 0 10027 ABC Vendor 108 108 0 0 10028 ABC Vendor 108 108 0 0 10029 ABC Vendor 84 84 0 0 10030 ABC Vendor 72 72 0 0 10031 ABC Vendor 60 60 0 0 10032 ABC Vendor 0 60 0 0 10033 ABC Vendor 54 54 0 0 10034 ABC Vendor 48 48 0 0 10035 ABC Vendor 36 36 0 0 10036 ABC Vendor 12 12 0 0 10037

If looks correct, but if i pull out the PO number from the table, the Short Ship PO Count will show me 37 which is counting all the PO but not 3.

PS. I am not allow to create variable in my APPS.

1 Solution

Accepted Solutions
Contributor III
Author

Thanks Channa for helping me out.

With below expression is now work! Thanks!

Count(Distinct {<[PO Number]={"=Sum({\$<Quantity = - {0}>} Quantity-[Original Quanity])<0"}>} [PO Number])

5 Replies
Specialist III

those are aggregate function they will show only 3 if you remove PONUmber

Sum({\$<Quantity = - {'0'}>}Quantity)

Sum({\$<Quantity = - {0}>}Quantity)

you dont need single codes it is numlber

Channa
Contributor III
Author

Thanks Channa.

I was thinking to use the AGGR function but I am not quite sure how i can apply aggr to below formula due to the complexity.

if(AGGR
( Sum({\$<Quantity = - {0}>}Quantity) ) -
( Sum({\$<Quantity = - {0}>}[Original Quanity]) ) < 0 ,
(Count (distinct [PO Number])),0)

If anyone can help and guide me how i can apply AGGR in this case?

Contributor III
Author

I really need some help on this as i am not sure how i can apply AGGR function with my case.

Contributor III
Author

I try something like below but receive error is expression.

SUM( Aggr
count (
if(
(( Sum({\$<Quantity = - {0}>}Quantity) ) - ( Sum({\$<Quantity = - {0}>}[Original Quanity]) ) )< 0 ,1,0),

distinct [PO Number])
)

Contributor III
Author

Thanks Channa for helping me out.

With below expression is now work! Thanks!

Count(Distinct {<[PO Number]={"=Sum({\$<Quantity = - {0}>} Quantity-[Original Quanity])<0"}>} [PO Number])

Tags
Community Browser