Calculate Average Turn Around Time without calculating zero days

[KevinJ]

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.]))

[Daniel_Pilla]

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,

[KevinJ]

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. 

[Daniel_Pilla]

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,

[Daniel_Pilla]

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

Cheers,

[KevinJ · Accepted solution]

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)

[Daniel_Pilla · Accepted solution]

@KevinJ ,

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

Cheers.