how to calculate stdev over last 500 records in script

[#1]

I need to calculate rolling  stdev for the last 500 days of my table. Solutions -

1. I can use peek, put 500 of them in a rangestdev(...) that is ugly but might work. It might be very slow.

2. I am wondering if there is a way to do this in vbscript. I see an issue. That function has to be passed 500 records.

3. Another way might be to do the calculation in a sub, which will be very efficient but then  it will return the stdev for each record that has to be replaced into the table. I do not know if Qlik can handle that.

Any suggestions?

Thanks.

[Anonymous]

maybe something like this?

Table1:

LOAD

     Field1,

     Field2,

     Field3,

     Date

     Sales

From abc.xls

Order By Date desc;

ST:

LOAD

     Stdev(Sales) as STDEV

Resident Table1

Where RowNo()<=500;

[Gysbert_Wassenaar]

Create an AsOf table like explained in this blog post The As-Of Table‌. Then join that AsOf table with the values you want to calculate the stdev over. Finally create a summary table with the rolling stdev values.

AsOf:

LOAD DISTINCT

     MyDate as ReportDate, Date(MyDate - IterNo() + 1) as MyDate

FROM

     MySource

WHILE IterNo() <= 500;

JOIN (AsOf)

LOAD

     MyDate,

     Value

FROM

     MySource;

Result:

LOAD

     ReportDate,

     Stdev(Value) as StDev500

RESIDENT

     AsOf

GROUP BY

     ReportDate;

DROP TABLE AsOf;

[engishfaque]

Dear Varum,

Go to your edit script and write down "First 500" for loading 500 records, given below is the example for better understanding.

Example:

First 500

Load

          *

From Your_Data_Source;

Kind regards,

Ishfaque Ahmed

[#5]

Thanks. That is a great idea.

This scheme of generating dates like Date(MyDate - IterNo() + 1) as MyDate work if all dates were sequential. In my set, I have gaps in dates, so the date cannot be created but has to be picked from the existing set.

[Gysbert_Wassenaar]

The first create a counter field in the script to create sequential numbers. Perhaps you can use the RecNo or RowNo functions or the autonumber function.

[#7]

Thanks Gysbert

I have tried to do that. But am experiencing difficulty. I ended up with joining the data over and over, but doing it 500 times is a bit much.

If you can build your idea as to how it work will be useful.

In the meantime, I went outside qlik to solve the problem.

Thanks.

[Gysbert_Wassenaar · Accepted solution]

Maybe like this:

Temp:

LOAD * FROM MySource.qvd (qvd); // <- this is just an example, replace it with your real data source

MyTable

LOAD MyDate, AutoNumber(MyDate) as DateID

RESIDENT MySource

Order By MyDate;

DROP TABLE Temp;

AsOf:

LOAD DISTINCT MyDate as ReportDate, DateID + 1 - IterNo() as DateID

RESIDENT MySource

WHILE IterNo() <= RangeMin(DateId,500);

[#9]

I created a sample qvw implementing your ideas. I will post later.