Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My data:
Ship Req # | Shipped Date | Item Code | Description | [Bill Number] | Shipped Value | Shipped Qty |
11373 | 10/28/2015 | 209104 | Acme Apple | 9540 | 299 | 597 |
11371 | 10/28/2015 | 386008 | Charles Cat | 9537 | 1,121 | 26,568 |
11371 | 10/28/2015 | 386009 | Charles Cat | 9537 | 2,296 | 54,432 |
11309 | 10/28/2015 | 109115A | Doug's Dog | 9538 | 972 | 528 |
11376 | 11/02/2015 | 103089 | Acme Green Apple | 9541 | 14,564 | 2,896 |
11432 | 11/03/2015 | 581003 | Farmers Fox | - | 679 | 2,380 |
11450 | 11/03/2015 | 103089 | Acme Green Apple | 9541 | 14,564 | 2,896 |
The "Bill Number" column is in a separate column from the other items which are all in one table.
I am trying to create a "Shipped Not Invoiced" chart. The result should be the one line with Ship Req # 11432. Since an invoice has not been created, the Bill Number does not exist.
The code to create the Shipped Value is
sum( $(_SetShippedActualDate)
IF($(_SysDBVersionNum)>=13.2,
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] /
[Shipping Price Unit Conversion]),
([Shipped Picked Quantity] *
[Shipping Unit Price] *
[Shipping Rate $(_SysBaseCurrency)] *
[Shipping Price Unit Conversion])
)
)
How do I sum all "Ship Req #" that do not have a corresponding "Bill Number"?
Thank you for your help!
you can use something like if(isnull(billnumber),'Not Invoiced','Invoiced') as a flag or you can put this as an and condition 'and isnull(billnumber)' in your condition
How do I sum all "Ship Req #" that do not have a corresponding "Bill Number"?
Do you want to sum up all the numbers which are like IDs ?
Any ways if you want sum up a field Ship Req # that do not have a corresponding "Bill Number" then :
if(len([Bill Number])=0, Sum([Ship Req #])) - in both script and UI
should work.
Let me know.