Using functions across multiple aggregations

[#1]

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

[mrossoit]

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

[#3]

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)

)

[ramoncova06 · Accepted solution]

use Aggr

Aggr() function

[shanemichelon]

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

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

[#6]

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