topic weighted averages with aggr in QlikView

weighted averages with aggr

chematos — Mon, 04 Jun 2012 16:54:18 GMT

Hi, I´m trying to use aggr function to calculate weighted averages.

I have data like this:

I/D

LINEA

CENTRO

TIPO Promedio

AñoPeriodo

nPeriodo

Mes

%Fecha

DIRECTO	LECHE	700042	Efectivo	0.61	2010	1	Ago	31/08/2010
DIRECTO	LECHE	700042	Efectivo	0.74	2010	1	Ago	31/08/2010
DIRECTO	LECHE	700042	Zafral	0.74	2010	1	Ago	31/08/2010
DIRECTO	LECHE	700042	Efectivo	0.77	2010	1	Ago	31/08/2010
DIRECTO	LECHE	700042	Efectivo	0.81	2010	1	Ago	31/08/2010
DIRECTO	LECHE	700042	Efectivo	0.8	2010	2	Sep	30/09/2010
DIRECTO	LECHE	700042	Efectivo	0.87	2010	2	Sep	30/09/2010
DIRECTO	LECHE	700042	Zafral	0.87	2010	2	Sep	30/09/2010
DIRECTO	LECHE	700042	Efectivo	0.68	2010	3	Oct	31/10/2010
DIRECTO	LECHE	700042	Efectivo	0.74	2010	3	Oct	31/10/2010
DIRECTO	LECHE	700042	Efectivo	0.77	2010	3	Oct	31/10/2010
DIRECTO	LECHE	700042	Efectivo	0.81	2010	3	Oct	31/10/2010
DIRECTO	LECHE	700042	Efectivo	0.84	2010	3	Oct	31/10/2010
DIRECTO	LECHE	700042	Zafral	0.84	2010	3	Oct	31/10/2010

Date	Days
31/08/2010	31
30/09/2010	30
31/10/2010	31

This is a particular case with a few months, so what I need is to add the averages (field Promedio) of the month multiplied by the days of the month for each month since Agoust to October and divide that result by the total days of those months

	Avg Month	Days Month	*Avg MonthDays Month**	Total Avgs	Total Days	weighted average
Ago	3,67	31	113,77	335,05	92	3,64
Sep	2,54	30	76,2
Oct	4,68	31	145,08

This is my first aggr function and I have a doubt apart of all this, in the aggr function, the dimensions would be like a group by??

Well, this is my function and it´s not working at all.

sum(aggr(sum({<Periodo={'<=$(=Periodo)'},AñoPeriodo={'=$(=AñoPeriodo)'},PERIODO=>}Promedio)

,%Fecha,CENTRO,TIPO,nPeriodo) * DaysMonth)

sum({<Periodo={'<=$(=Periodo)'},AñoEjercicio={'=$(=AñoEjercicio)'},PERIODO=>}DaysMonth)

Regards,

Chema.

weighted averages with aggr

Oleg_Troyansky — Mon, 04 Jun 2012 17:01:57 GMT

I'm assuming that the two tables that you described, are associated by Date. If they are not linked, the following won't work correctly. You have to make sure that the two tables are linked.

Then, try including the multiplication by DaysMonth inside of your AGGR calculation:

sum(aggr(sum({<Periodo={'<=$(=Periodo)'},AñoPeriodo={'=$(=AñoPeriodo)'},PERIODO=>}

Promedio* DaysMonth)

,%Fecha,CENTRO,TIPO,nPeriodo) )

sum({<Periodo={'<=$(=Periodo)'},AñoEjercicio={'=$(=AñoEjercicio)'},PERIODO=>}DaysMonth)

weighted averages with aggr

chematos — Mon, 04 Jun 2012 17:20:27 GMT

You are assuming well, they are linked by Date.

I´ve tried what you said but I`m getting incorrect results.

thank´s

weighted averages with aggr

Oleg_Troyansky — Mon, 04 Jun 2012 17:28:56 GMT

I'm not sure why you have different Set Analysis condition in the numerator and in the denominator - usually the conditions should be the same for the weighted average logic to work correctly.

If you post an example, we could try and and find the problem, otherwise it's very hard to guess...

weighted averages with aggr

chematos — Mon, 04 Jun 2012 18:14:16 GMT

I know but I can´t upload it right now. The behaviour is as follows:

User selects a Period, imagine that is October 2010, so because my fiscal year starts in August, I need to make calculations between those months in the same year, that´s the reason of my conditions in numerator and denominator: {<Periodo={'<=$(=Periodo)'},AñoPeriodo={'=$(=AñoPeriodo)'},PERIODO=>} //Periodo or Month is the same because they are linked in my calendar so Perdiodo =1 is Month = August

%Fecha,CENTRO,TIPO,nPeriodo //with these dimensions I want to add the averages in the numerator by month but each month multiplied by days of month:

Avg August * 31 + Avg September * 30 + Avg October *31

and the denominator must be 31+30+31, total days of the three months.

If the syntax of my expression is correct, I need to pay more attention on the relationships of my tables but I don´t see anything strange...

May be I need to calculate something in the load, I don´t know wich is the best way to resolve it.

Thank you anyway.

weighted averages with aggr

chematos — Mon, 04 Jun 2012 20:18:25 GMT

Finally, I resolved it without aggr function but I´ve learned about that, so I´m happy!!

If anyone is interested, the best way I found is creating other table in the load script with partial sums for each month like: sum(Promedio)*DaysMonth grouping by Month.

So we have in another table the Month or Date with DaysMonth.

Finally, this would be the expression where we add all month averages until the month selected and depending on the dimensionality, we calculate the weighted average dividing by Total Days until the month selected or we calculate the simple average dividing by the number of months until the month selected.

if(Dimensionality()=5,

sum({<Period={'<=$(=Period)'},FiscalYear={'=$(=FiscalYear)'},PERIOD=>}[Avg Month])

sum({<Period={'<=$(=Period)'},FiscalYear={'=$(=FiscalYear)'},PERIOD=>}DaysMonth)

,sum({<Period={'<=$(=Period)'},FiscalYear={'=$(=FiscalYear)'},PERIOD=>}Avg)

nPeriod)

Regards.