topic Re: Using functions across multiple aggregations in QlikView

Using functions across multiple aggregations

Tue, 24 Mar 2015 21:22:53 GMT

Hi all,

I'm new to scripting in qlik and I'm currently building an app in Qlik Sense. I'm wondering if its possible to use a function across multiple aggregations. For example, I want to know how many standard deviations an average score in 2014 is from the average of yearly averages from 2008-2013. Although I'm not keen on averaging averages, I have different populations being sample each year so it seems reasonable. I have managed to get this to work but I've had to repeat the same set analysis lines over and over in finding the average between 2008-2013 when calculating the standard deviation. I tried using the function stdev over the 2008-2013 aggregates, but that doesn't work. I was also surprised that I couldn't square the differences between the 2014 average and historical average by putting the whole thing in brackets followed with ^2. Is there a separate set of functions for use on aggregations, or do calculations across aggregates simply need to be coded?

Apologies in advance if this has already been asked, I've been looking for for a few days on the forum but I couldn't find anything relevant.

Cheers,

Taylor

Re: Using functions across multiple aggregations

mrossoit — Tue, 24 Mar 2015 23:54:16 GMT

Hi Taylor,

post your script or your qvw, please.

If you would explain through comments what is the result you want to reach, it could be easier to help you

Re: Using functions across multiple aggregations

Wed, 25 Mar 2015 00:16:09 GMT

Thanks, I've attached my script. OUTPUT is the measurement across the dimension YEAR. In this script I'm dividing the difference between the average of 2014 and the average across averages 2008-2013 by the standard deviation of the average across averages 2008-2013.

My question is whether there is any easier way to script what I'm doing by using functions that work with the aggregates. The following doesn't work but this is what I'm after rather than having to write out the formula completely:

stdev(

avg({$<YEAR={2013}>}OUTPUT)+

avg({$<YEAR={2012}>}OUTPUT)+

avg({$<YEAR={2011}>}OUTPUT)+

avg({$<YEAR={2010}>}OUTPUT)+

avg({$<YEAR={2009}>}OUTPUT)+

avg({$<YEAR={2008}>}OUTPUT)

)

Re: Using functions across multiple aggregations

ramoncova06 — Wed, 25 Mar 2015 03:31:52 GMT

use Aggr

Aggr() function

Re: Using functions across multiple aggregations

shanemichelon — Wed, 25 Mar 2015 03:39:54 GMT

For your averages, can't you just use the average of the previous years:

AVG({$ <Year="<={2013}">} OUTPUT)

Re: Using functions across multiple aggregations

Thu, 26 Mar 2015 09:09:17 GMT

Thanks Ramon and Shane, that's exactly what I was looking for!