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

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

sarahallen1 — Thu, 25 Sep 2014 07:56:29 GMT

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!

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

Anonymous — Thu, 25 Sep 2014 12:05:40 GMT

Hi,

I build a script to do this. Is this what you wanted?

Best

Stefan

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

sarahallen1 — Thu, 25 Sep 2014 15:26:10 GMT

Hi Stefan,

Thanks very much for this! It works.

I did not realise you could use aggregation functions like stdev in the script without a group by. All the QV documentation I've seen says that the load statement has to be a group by for aggregations to be allowed. I suppose what's happening in your script is the where clause gathers multiple records, and as you are not loading any fields based on single records, it kind of thinks it's a group (?!)

Anyway, it baffled me a bit at first but I think I see how/why it works now, and the most important thing is that it does work! So thank you.

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

Thu, 25 Sep 2014 15:47:00 GMT

Hi Stefan,

I am trying to understand your logic.

If possible, can you pls explain what below logic will do?

Leer:

load

Null() as MonthEnd

AutoGenerate(0);

for i = 0 to (NoOfRows('36')-1)

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

sarahallen1 — Thu, 25 Sep 2014 15:56:23 GMT

That bit just creates a blank table with one field (MonthEnd) but no records.

From what I understand, it's just so there is something to concatenate to - if you look further down the script, within the loop (for each i.... next), it does a concatenation to the Leer table. It builds up that table row by row.

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

Anonymous — Thu, 25 Sep 2014 16:03:41 GMT

HI,

we don't need group by because we only have one logical MonthEnddate for each iteration, therefore we do not need to group by field values.

Best regards

Stefan

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

Anonymous — Thu, 25 Sep 2014 16:07:02 GMT

you are right!

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

Thu, 25 Sep 2014 16:24:07 GMT

Thanks, I got it.

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

sarahallen1 — Thu, 25 Sep 2014 16:49:32 GMT

The QVW I sent above was a simplified use case. I've just realised that maybe I simplified it too much, but I have still managed to make it work - elaborating here in case other readers find it useful.

I simplified it because I removed another column in the original data, "fund" (each fund has it's own set of monthly returns). I need this fund field to split up the data in the final table, as the stdev is calculated for each fund for each month-end, not just for each month-end.

My solution was to repeat the peek function to get another variable for the value of the field fund. Same idea as the month-end peek variable - you're just reading values from a field, but can't do that in the load statement without it needing a group by statement.