8 Replies Latest reply: Sep 25, 2014 12:49 PM by Sarah Hymers RSS

    Aggregation in script across multiple previous records WITHOUT intervalmatch/grouping?

    Sarah Hymers

      I have a calculation in a chart which works well using set analysis.  I want to get the calculation into the script as a new field for a variety of reasons which I won't bore you with...

       

      I'm struggling to find an equivalent of the set analysis in the script.  I have one suggestion of how it can work but it seems very inefficient: I'd do an interval match to associate all the records (which the set analysis would define), and then group by to do my aggregation.  But that's growing my dataset 36x just to shrink it back again.

       

      Is there something with previous() or above() or peek() that I can use?  Sure there must be something out there!

       

      Any help is much appreciated, thanks.

       

       

      Simplified situation (see attached QVW):

      One table, 2 fields: [month-end], [monthly return].

      For a given month-end, the standard deviation for 36 months to date needs to be calculated.

      So we need a new table with 2 fields: [month-end], [36m standard deviation].

      Using set analysis this is possible in a chart (see QVW) - although I haven't yet tried making it work with a month-end dimension (have done it rather lazily with variables, to pin down a single month-end).  But how do I do it in the script? 

      I could use some sort of intervalmatch to associate each month-end with 36 other month-ends leading up to it (say a field [associated month]), and then group by [month-end] and calculate stdev([monthly return]) which will be over 36 records.  But that grows my data 36x (in interval match) and then shrinks it again (in grouping).  Seems inefficient!