Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table like below.
Fields Total Quantity expression : Sum({$<Quantity = - {'0'}>}Quantity)
Field Original Quantity : Sum([Original Quanity])
Field Short Ship QTY expression : Sum({$<Quantity = - {'0'}>} Quantity-[Original Quanity])
Field Short Ship PO COUNT expression:
if(
( Sum({$<Quantity = - {'0'}>}Quantity) ) -
( Sum({$<Quantity = - {'0'}>}[Original Quanity]) ) < 0 ,
(Count (distinct [PO Number])),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.
Can you please help me to figure it out where is the problem from?
PS. I am not allow to create variable in my APPS.
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])
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
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?
I really need some help on this as i am not sure how i can apply AGGR function with my case.
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])
)
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])