topic sumproduct or aggr or calculations in the load script in QlikView

sumproduct or aggr or calculations in the load script

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

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 — Wed, 07 Mar 2018 18:16:05 GMT

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 — Fri, 09 Mar 2018 07:52:33 GMT

thanks, Sunny! it was helpful