topic Re: OTD % in QlikView

OTD %

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Can someone help me to create a formula to represent %OTD?

I have:

OTD =PO SHIPMENT REQUEST DATE- PO SHIP DATE

+-3 days is LATE DELIVERY

I am looking for a column that shows : LATE or ON TIME

As well be able to add a chart.

Thank you very much for your help

Re: OTD %

sunny_talwar — Wed, 30 Aug 2017 15:01:31 GMT

Would you be able to share some raw data with the output you expect to see from it?

Re: OTD %

Anil_Babu_Samineni — Thu, 31 Aug 2017 13:26:39 GMT

For %OTD you could use something like below?

Sum(OTD) / Count(OTD)

Re: OTD %

jonathandienst — Thu, 31 Aug 2017 13:29:03 GMT

Something like this, perhaps?

Count(If([PO SHIP DATE] - [PO SHIPMENT REQUEST DATE] > 3, [PO NUMBER])) / Count([PO NUMBER])

(assumes that a shipment has an id like PO NUMBER. Replace this with the correct field name)

Re: OTD %

its_anandrjs — Thu, 31 Aug 2017 13:38:19 GMT

You can try some this ways

Load

If( (PO SHIPMENT REQUEST DATE- PO SHIP DATE ) > 3 ,'LATE','On TIME') as DeliveryStatus

From Source;

Load

If( (Interval( PO SHIPMENT REQUEST DATE- PO SHIP DATE,' D hh:mm ' ) ) > 3 ,'LATE','On TIME') as DeliveryStatus

From Source;

Re: OTD %

Kushal_Chawda — Thu, 31 Aug 2017 13:47:32 GMT

I would suggest to create the Flag in script to make the expression more optmized

LOAD *,

if(PO SHIPMENT REQUEST DATE- PO SHIP DATE <=-3, 1,0) as isShipmentLate

FROM table;

Now you can put the expression like below chart

=count(distinct {<isShipmentLate={1}>} ID) / count( distinct ID)

You can remove the DISTINCT keyword from expression based on your data.

Here ID is the field which you want to count.

Or if you want the % based on Value, use below

=sum({<isShipmentLate={1}>} Value) / sum( Value)

Re: OTD %

Anonymous — Mon, 12 Mar 2018 20:13:56 GMT

@stalwar1 Finally back...my apologies for my delay. Please see attached table that i was able to create with everyone's advises.

I want to add to the Gauge chart a graph showing the trend of the previous months.

Can you please help me?

I update the OTD =<7 days vs Required Date ( changed from 3 days)

thanks,