Script Expression Error

[#1]

Hey guys.

Im trying to find the on tme shipping % can anyone help me? this is my formula in script:

if(ShipDate-DueDate<=0,'On-Time','Late') as ShipmentStatus,

num(count(if(ShipDate-DueDate<=0,'On-Time','Late')'On-Time',/if(ShipDate-DueDate<=0,'On-Time','Late')'Late'),'#0%') as OnTime%

And this is the error:

Please help me

[#2]

anyone?

[#3]

Hi Blaze,

You could try use : count( {$<ShipmentStatus = {"On-Time"}>} value ) for the On-Time Count

Then another expression for Late : count( {$<ShipmentStatus = {"Late"}>} value )

[#4]

Hey Xena.

Thanks for the reply. I tried :

=count( {$<ShipmentStatus = {"On-Time"}>})/count( {$<ShipmentStatus = {'Late'}>})

but it doesn't work.I need to take the total shipments made, whether they were late or not, and then divide by the number of late shipments to work out the On-time %. It also needs to be done in the script. Have u maybe got any other ideas? Thanks so much for your help i really appreciate it.

[#5 · Accepted solution]

Hi Blaze,

Let us say you want to calculate the OnTime Shipment %. You have some fields InvNo, InvDt, ShipDate, DueDate, InvAmount, InvQty.

INVOICE_DETAILS:

LOAD CustomerCode,

InvNo,

InvDt,

ShipDate,

DueDate,

InvAmount,

InvQty,

If( ShipDate - DueDate <= 0,'On Time', 'Late') AS ShipStatus

FROM INVOICE_DETAILS.xls;

In the graphs (front end), you can write the expressions

= COUNT(DISTINCT If( ShipStatus = 'On Time', InvNo )) / COUNT( DISTINCT InvNo)

Let us say that you have 100 invoices out of which 60 was delievered on time and 40 was late delievery.

This expression will give you 60%.

Hope, it would helpful for you.

Thanks & Regards,

Raj Kishor

[#6]

You can add this small bit into your script.

[#7]

Hey Raj.

Thanks for the reply. I understand what you mean about the Invno, etc. I used this:

= COUNT(DISTINCT If( ShipmentStatus = 'On Time', PurchaseOrderNumber )) / COUNT( DISTINCT PurchaseOrderNumber)

Where PurchaseOrderNumber was the Order number and it still gives me -?

Any Ideas?

[#8]

Hey Raj.

Your Formula worked. It was on my side. I wrote 'On Time' instead of 'On-Time'. My bad 🙂

Correct formula is this:

=num(COUNT(DISTINCT If( ShipmentStatus = 'On-Time', PurchaseOrderNumber ))/COUNT( DISTINCT PurchaseOrderNumber),'##,0%')

Thanks again for the help guys.