Calculate weighted average for a group

midnight7699 — Sat, 16 Nov 2024 17:36:08 GMT

Hi there,

I have a dataset like this (lots more rows and columns)

ID	Status (N or U)	Age	Inscription Date
1	N	19	2020/12/03
2	U	58	2020/09/07
3	N	45	2018/07/09
4	U	40	2019/01/02

I want to calculate the weighted average of age for each group of N or U (group in status) and for each period (2018-2019-2020), also the weighted average of age for each group of N or U during each quarter since 2018.

I'm newbie in QlikView so it'd be really grateful if someone can leave some comments here.

Thanks!!

Re: Calculate weighted average for a group

QFabian — Wed, 20 Jan 2021 21:41:24 GMT

HI @midnight7699 , is this what you want? please check the script:

Data:
LOAD * INLINE [
ID, Status, Age, InscriptionDate
1, N, 19, 2020/12/03
2, U, 58, 2020/09/07
3, N, 45, 2018/07/09
4, U, 40, 2019/01/02
];

B:
Load
ID,
Status,
Age,
InscriptionDate,
QuarterName(InscriptionDate) as Quarter,
year(InscriptionDate) as Year
Resident Data;

Status:
Load
Status,
avg(Age) as AgeAvgStatus
Resident B
group by Status;

Quarter:
Load
Quarter,
avg(Age) as AgeAvgQuarter

Resident B
group by Quarter;

Year:
Load
Year,
avg(Age) as AgeAvgYear

Resident B
group by Year;

drop table Data;

that script produces the following data model :

topic Calculate weighted average for a group in QlikView

Calculate weighted average for a group

Re: Calculate weighted average for a group