WEIGHTED AVERAGE AND IF STATMENT

[Catherine_CAS]

Need to plot bar chart to compare weighted average hours for 2020 vs 2019 Products.

1) Calculate weighted average hours for2020 Products

2) Calculate weighted average hours for 2019 Products if 2020 Products QTY is greater than 0.

which mean for product E, sum of 2020 QTY is 0. It is excluded from 2019 weighted average.

Month	ORDER	Product Name	HOURS	QTY	2019 HPP
Jan	1	Product A	10	0	40
Jan	2	Product B	150	2	50
Jan	3	Product C	200	3	60
Jan	4	Product D	100	4	70
Jan	5	Product E	50	0	10
Jan	6	Product A	200	6	40
Jan	7	Product B	100	7	50
Jan	8	Product C	150	2	60
Jan	9	Product D	200	1	70
Jan	10	Product E	200	0	10

Below is the correct value:

2020 AVERAGE HOURS	48.19
2019 AVERAGE HOURS	55

[Ksrinivasan]

hhi,

provide adequate data,

where 2020 data, how you have arrived correct value that 48.19 and 55

ksrinivasan

[Catherine_CAS]

Apologies.. missing out the text in the header. I have attached my working excel for your reference.

[Ksrinivasan]

hi,

kindly find the result chart and script as below

Script it may workaround solution

cath:
LOAD
Month,
"Product Name",
"2020 HOURS",
"2020 QTY",
"2019 HPP" as "2019 HPP"
FROM [lib://REPORT EXTRACTION TEST/SSSS1.xlsx]
(ooxml, embedded labels, table is cat);
TEMP:
NoConcatenate
LOAD
"Product Name",
SUM("2020 QTY") AS QTY,
SUM("2020 HOURS")/SUM("2020 QTY") AS HOURS_2020,
if(SUM("2020 QTY")>'0',SUM("2019 HPP")/2,'0') AS HOURS_2019
Resident [cath]
GROUP BY "Product Name";
Store TEMP into [lib://REPORT EXTRACTION /TEST.qvd](qvd);
Drop table cath;
drop table TEMP;
Final:
LOAD
'Product' as Product,
"Product Name",
QTY,
HOURS_2020,
HOURS_2019
FROM [lib://REPORT EXTRACTION/TEST.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq)
WHERE NOT MATCH("QTY", '0');

ksrinivasan