topic Re: AVG Group By in QlikView

AVG Group By

Thu, 01 Oct 2015 15:00:42 GMT

SOS, I could not find the syntax error or process that does not allow me to calculate:

avg (ies_AñosAcreditacion) * 100 as ies_AñosAcreditacionVariacion2

Thank you

--------------------------------------------------------------------------------

IES_Indicadores:

LOAD *

FROM $(PathQVD)IES_Detalles.QVD

(qvd);

ES_Indicadores2:

LOAD

ies_Version,

ies_Codigo,

ies_Nombre,

ies_Nemonico,

ies_Agrupacion,

ies_SubAgrupacion,

ies_Regionales,

ies_AdscritasAdmision,

ies_AñosAcreditacion,

avg(ies_AñosAcreditacion) * 100 as ies_AñosAcreditacionVariacion2

Resident IES_Indicadores Group By ies_Version;

Re: AVG Group By

diego_a_barboza — Thu, 01 Oct 2015 21:05:46 GMT

You need to group your data, otherwise it will not be capable to show the average.

ES_Indicadores2:

LOAD

ies_Version,

ies_Codigo,

ies_Nombre,

ies_Nemonico,

ies_Agrupacion,

ies_SubAgrupacion,

ies_Regionales,

ies_AdscritasAdmision,

ies_AñosAcreditacion,

avg(ies_AñosAcreditacion) * 100 as ies_AñosAcreditacionVariacion2

Resident IES_Indicadores Group By ies_Version

group by

ies_Version,

ies_Codigo,

ies_Nombre,

ies_Nemonico,

ies_Agrupacion,

ies_SubAgrupacion,

ies_Regionales,

ies_AdscritasAdmision,

ies_AñosAcreditacion;

An alternative would be creating an auxiliar table with the averages... however, in my humble opinion, it's much better to let QlikView calculate the average on the object itself (chart/table/etc...)

Re: AVG Group By

swuehl — Thu, 01 Oct 2015 21:12:11 GMT

You need to list all LOADed fields in the GROUP BY clause that are not aggregated (or remove them from the LOAD or aggregate them):

ES_Indicadores2:

LOAD

ies_Version,

ies_Codigo,

ies_Nombre,

ies_Nemonico,

ies_Agrupacion,

ies_SubAgrupacion,

ies_Regionales,

ies_AdscritasAdmision,

ies_AñosAcreditacion,

avg(ies_AñosAcreditacion) * 100 as ies_AñosAcreditacionVariacion2

Resident IES_Indicadores

Group By ies_Version,

ies_Codigo,

ies_Nombre,

ies_Nemonico,

ies_Agrupacion,

ies_SubAgrupacion,

ies_Regionales,

ies_AdscritasAdmision,

ies_AñosAcreditacion

;