sumproduct or aggr or calculations in the load script

[andreip21]

Hi guys,

I have the following database:

Order Date || Order ID || est. Delivery Date Start || est. Delivery Date end || min. Delivery Time (days) || max. Delivery Time (days)

5 feb || 1 || 06 feb || 07 feb || 1 || 2

5 feb || 2 || 07 feb || 08 feb || 1 || 3

6 feb || 3 || 06 feb || 07 feb || 0 || 1

6 feb || 4 || 08 feb || 09 feb || 2 || 3

6 feb || 5 || 08 feb || 10 feb || 2 || 4

7 feb || 6 || 10 feb || 10 feb || 3 || 3

7 feb || 7 || 09 feb || 10 feb || 2 || 3

the last 2 columns are the differences: est. Delivery Date - Order date

I want to calculate in a new chart, for each day, the weighted average of the Delivery time:

sum (Delivery TIme)/ count (Order ID)

both min and max.

and to obtain the following results:

Order Date || min. Delivery Time || max. Delivery Time

5 feb || (1+1)/2 = 1           || (2+3)/2 = 2.5

6 feb || (0+2+2)/3 = 1.33  || (1+3+4)/3 = 2.66

7 feb || (3+2)/2 = 2.5        || (3+3)/2 = 3

How do i do that?

i attached a qvw, if it's easier for you to help me

Regards,

Andrei

[sunny_talwar · Accepted solution]

May be this

database: 

LOAD * INLINE [

    Order Date, Order ID, est. Delivery Date Start, est. Delivery Date end, min. Delivery Time (days), max. Delivery Time (days)

    5 feb, 1, 06 feb, 07 feb, 1, 2

    5 feb, 2, 07 feb, 08 feb, 1, 3

    6 feb, 3, 06 feb, 07 feb, 0, 1

    6 feb, 4, 08 feb, 09 feb, 2, 3

    6 feb, 5, 08 feb, 10 feb, 2, 4

    7 feb, 6, 10 feb, 10 feb, 3, 3

    7 feb, 7, 09 feb, 10 feb, 2, 3

]; 

Left Join (database)

LOAD [Order Date],

Sum([min. Delivery Time (days)])/Count([Order ID]) as [min. Delivery Time],

Sum([max. Delivery Time (days)])/Count([Order ID]) as [max. Delivery Time]

Resident database

Group By [Order Date];

[andreip21]

thanks, Sunny! it was helpful