topic Re: Calculate Average Turn Around Time without calculating zero days in App Development

Calculate Average Turn Around Time without calculating zero days

KevinJ — Fri, 15 Nov 2024 21:10:17 GMT

I want to have the average turn around time per purchase order calculating 0

I have this columns

Purchase Order No.	Received Date1	Order Date1	DateDiff
WH0023410801	01/12/2023	03/11/2023	28
WH0023401601	19/11/2023	31/10/2023	19
WH0023186502	18/11/2023	03/11/2023	15
WH0023173701	17/11/2023	03/11/2023	14
WH0023338502	15/11/2023	03/11/2023	12
WH0023390801	15/11/2023	03/11/2023	12
WH0023298501	14/11/2023	03/11/2023	11
WH0123967401	13/11/2023	03/11/2023	10
WH0123967402	09/11/2023	03/11/2023	6
SP1923030101	08/11/2023	04/11/2023	4
WH0023186504	07/11/2023	03/11/2023	4
WH0023305703	06/11/2023	02/11/2023	4
WH0023318101	07/11/2023	03/11/2023	4
WH0023173702	06/11/2023	03/11/2023	3
WH0023173702	06/11/2023	03/11/2023	3
WH0023186503	06/11/2023	03/11/2023	3
WH0023382401	03/11/2023	02/11/2023	1
WH0023390701	04/11/2023	03/11/2023	1
WH0023390802	04/11/2023	03/11/2023	1
HOFC23969301	31/10/2023	31/10/2023	0
HOFC23835801	08/09/2023	11/09/2023	-3
WH0023133203	-	14/11/2023	-
WH0023323501	-	04/10/2023	-

		Expected result	8.157894737

do not calculate the negative and also 0

I already have this formula but it gives the wrong result

Avg(Aggr(sum(date(daystart([Received Date1]),'MM/DD/YYYY') - date(daystart([Order Date1]),'MM/DD/YYYY')),[Purchase Order No.]))

Re: Calculate Average Turn Around Time without calculating zero days

Daniel_Pilla — Fri, 15 Dec 2023 17:37:08 GMT

Hi @KevinJ ,

You have two records with the ID `WH0023173702`.

WH0023173702	06/11/2023	03/11/2023	3
WH0023173702	06/11/2023	03/11/2023	3

You are averaging these twice in your expected result, which is making it lower (8.16 vs 8.44). Is that desired? Seems like a duplicate record.

Here is the expression which I am using (which yields 8.44 as it only includes the record once):

Avg( Aggr( If([Received Date1] - [Order Date1]>0,[Received Date1] - [Order Date1]), [Purchase Order No.] ) )

This is something that I would calculate in the script just like you have your "DateDiff" field. I'd then create a flag field for the differences you don't want, like below:

Data: LOAD *, If(DateDiff>0,1,0) AS DateDiffFlag ; LOAD *, [Received Date1] - [Order Date1] AS DateDiff INLINE [ Purchase Order No. ,Received Date1 ,Order Date1 WH0023410801 ,01/12/2023 ,03/11/2023 WH0023401601 ,19/11/2023 ,31/10/2023 WH0023186502 ,18/11/2023 ,03/11/2023 WH0023173701 ,17/11/2023 ,03/11/2023 WH0023338502 ,15/11/2023 ,03/11/2023 WH0023390801 ,15/11/2023 ,03/11/2023 WH0023298501 ,14/11/2023 ,03/11/2023 WH0123967401 ,13/11/2023 ,03/11/2023 WH0123967402 ,09/11/2023 ,03/11/2023 SP1923030101 ,08/11/2023 ,04/11/2023 WH0023186504 ,07/11/2023 ,03/11/2023 WH0023305703 ,06/11/2023 ,02/11/2023 WH0023318101 ,07/11/2023 ,03/11/2023 WH0023173702 ,06/11/2023 ,03/11/2023 WH0023173702 ,06/11/2023 ,03/11/2023 WH0023186503 ,06/11/2023 ,03/11/2023 WH0023382401 ,03/11/2023 ,02/11/2023 WH0023390701 ,04/11/2023 ,03/11/2023 WH0023390802 ,04/11/2023 ,03/11/2023 HOFC23969301 ,31/10/2023 ,31/10/2023 HOFC23835801 ,08/09/2023 ,11/09/2023 WH0023133203 , ,14/11/2023 WH0023323501 , ,04/10/2023 ];

The expression can then be modified to:

{<DateDiffFlag={1}>} Avg( Aggr( [Received Date1] - [Order Date1], [Purchase Order No.] ) )

Cheers,

Re: Calculate Average Turn Around Time without calculating zero days

KevinJ — Fri, 15 Dec 2023 17:50:30 GMT

Hi thank you for your response no its not a duplicate record there are Purchase Number that can be dupplicated but have different recieved date but same order date.

Re: Calculate Average Turn Around Time without calculating zero days

Daniel_Pilla — Fri, 15 Dec 2023 17:58:24 GMT

Hi @KevinJ ,

If it is not a duplicate, then you need another field such as "ShipID". Here is an example that yields your expected number in that case.

Load Script:

Data: LOAD *, If(DateDiff>0,1,0) AS DateDiffFlag ; LOAD *, [Received Date1] - [Order Date1] AS DateDiff, RecNo() AS ShipID INLINE [ Purchase Order No. ,Received Date1 ,Order Date1 WH0023410801 ,01/12/2023 ,03/11/2023 WH0023401601 ,19/11/2023 ,31/10/2023 WH0023186502 ,18/11/2023 ,03/11/2023 WH0023173701 ,17/11/2023 ,03/11/2023 WH0023338502 ,15/11/2023 ,03/11/2023 WH0023390801 ,15/11/2023 ,03/11/2023 WH0023298501 ,14/11/2023 ,03/11/2023 WH0123967401 ,13/11/2023 ,03/11/2023 WH0123967402 ,09/11/2023 ,03/11/2023 SP1923030101 ,08/11/2023 ,04/11/2023 WH0023186504 ,07/11/2023 ,03/11/2023 WH0023305703 ,06/11/2023 ,02/11/2023 WH0023318101 ,07/11/2023 ,03/11/2023 WH0023173702 ,06/11/2023 ,03/11/2023 WH0023173702 ,06/11/2023 ,03/11/2023 WH0023186503 ,06/11/2023 ,03/11/2023 WH0023382401 ,03/11/2023 ,02/11/2023 WH0023390701 ,04/11/2023 ,03/11/2023 WH0023390802 ,04/11/2023 ,03/11/2023 HOFC23969301 ,31/10/2023 ,31/10/2023 HOFC23835801 ,08/09/2023 ,11/09/2023 WH0023133203 , ,14/11/2023 WH0023323501 , ,04/10/2023 ];

Expression:

{<DateDiffFlag={1}>} Avg( Aggr( [Received Date1] - [Order Date1], [Purchase Order No.], ShipID ) )

Cheers,

Re: Calculate Average Turn Around Time without calculating zero days

Daniel_Pilla — Fri, 15 Dec 2023 19:51:18 GMT

Hi @KevinJ , please remember to mark the solution as accepted if it worked for you. Thank you.

Cheers,

Re: Calculate Average Turn Around Time without calculating zero days

KevinJ — Sun, 17 Dec 2023 15:39:08 GMT

Sorry for the late reply what is the inline for?

currently this is my load:

[SerticaDashboard]:

LOAD

Status,

[Supplier Name],

[Purchase Order Workflow],

[Purchase Type Name],

[Item Type],

[Purchase Order No.],

[Subtotal. Std.],

Subtotal,

Date([Received Date],'DD/MM/YYYY') as "Received Date1",

Date([Order Date],'DD/MM/YYYY') as "Order Date1",

[Approved By Users],

SubField( [Approved By Users], ',', -1) as Last_Approver,

[Created by User],

month([Approved Date]) as Month,

Day([Approved Date]) as Day,

Year([Approved Date]) as Year,

[Currency No.] as Currency,

[Std. Currency] as PHPCurrency,

[Technical Account Name]

FROM [LIB://qvd_datafiles\SerticaDashboard.qvd]

(qvd)

Re: Calculate Average Turn Around Time without calculating zero days

Daniel_Pilla — Mon, 18 Dec 2023 15:14:21 GMT

@KevinJ ,

The inline load is simply me loading in your data in the script itself vs from a file.

Cheers.