Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Compute Moving Average

7 posts in Objects Last posting was on 2009-09-11 16:26:49.0Z
neil Posted on 2009-09-04 09:06:53.0Z
Sender: 2768.4aa0d849.1804289383@sybase.com
From: Neil
Newsgroups: sybase.public.powerbuilder.objects
Subject: Compute Moving Average
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4aa0d8ad.276e.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Sep 2009 02:06:53 -0700
X-Trace: forums-1-dub 1252055213 10.22.241.41 (4 Sep 2009 02:06:53 -0700)
X-Original-Trace: 4 Sep 2009 02:06:53 -0700, 10.22.241.41
Lines: 7
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9884
Article PK: 737014

Hi,

I would like to ask if its possible to have a moving average
formula in a datawindow? Please give code snippet.

TIA,
Neil


fisher <fisher_NO Posted on 2009-09-04 11:17:56.0Z
From: fisher <fisher_NO@SPAM_star.wckp.lodz.pl_PLEASE>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.objects
Subject: Re: Compute Moving Average
References: <4aa0d8ad.276e.1681692777@sybase.com>
In-Reply-To: <4aa0d8ad.276e.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090903-0, 09/03/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa0f764$1@forums-1-dub>
Date: 4 Sep 2009 04:17:56 -0700
X-Trace: forums-1-dub 1252063076 10.22.241.152 (4 Sep 2009 04:17:56 -0700)
X-Original-Trace: 4 Sep 2009 04:17:56 -0700, vip152.sybase.com
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9885
Article PK: 737015

like CumulativeSum()/rownum() expression

Neil wrote:
> Hi,
>
> I would like to ask if its possible to have a moving average
> formula in a datawindow? Please give code snippet.
>
> TIA,
> Neil


fisher <fisher_NO Posted on 2009-09-04 12:59:58.0Z
From: fisher <fisher_NO@SPAM_star.wckp.lodz.pl_PLEASE>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.objects
Subject: Re: Compute Moving Average
References: <4aa0d8ad.276e.1681692777@sybase.com> <4aa0f764$1@forums-1-dub>
In-Reply-To: <4aa0f764$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090903-0, 09/03/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa10f4e$1@forums-1-dub>
Date: 4 Sep 2009 05:59:58 -0700
X-Trace: forums-1-dub 1252069198 10.22.241.152 (4 Sep 2009 05:59:58 -0700)
X-Original-Trace: 4 Sep 2009 05:59:58 -0700, vip152.sybase.com
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9886
Article PK: 737016

It should be getrow() instead of rownum()

I'm sorry to much Oracle/PB switching just before weekend starts

fisher wrote:
> like CumulativeSum()/rownum() expression
>
>
> Neil wrote:
>> Hi,
>>
>> I would like to ask if its possible to have a moving average
>> formula in a datawindow? Please give code snippet.
>>
>> TIA,
>> Neil


neil Posted on 2009-09-11 04:13:32.0Z
Sender: 7d78.4aa9cc1a.1804289383@sybase.com
From: Neil
Newsgroups: sybase.public.powerbuilder.objects
Subject: Re: Compute Moving Average
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4aa9ce6c.7db5.1681692777@sybase.com>
References: <4aa10f4e$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 10 Sep 2009 21:13:32 -0700
X-Trace: forums-1-dub 1252642412 10.22.241.41 (10 Sep 2009 21:13:32 -0700)
X-Original-Trace: 10 Sep 2009 21:13:32 -0700, 10.22.241.41
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9889
Article PK: 737020

I think what I need is a little different from this
formula...

Example:
In a datawindow, I have a number column col1 with 100 rows
and I want a computed column like a moving average with the
LENGTH of 10.

This computed field will start to have a value at row 10,
which it sum-up col1 from row 1 to row 10 divide by 10...

At row 11, it will summ-up row 2 to row 11, divide by 10.
At row 12, it will summ-up row 3 to row 12, divide by 10.
At row 13, it will summ-up row 4 to row 13, divide by 10.

At 100th row, it summ-up from row 91 to row 100 divide by
10.

This is what I mean as "moving"... the row keeps moving.

> It should be getrow() instead of rownum()
>
> I'm sorry to much Oracle/PB switching just before weekend
> starts
>
> fisher wrote:
> > like CumulativeSum()/rownum() expression
> >
> >
> > Neil wrote:
> >> Hi,
> >>
> >> I would like to ask if its possible to have a moving
> average >> formula in a datawindow? Please give code
> snippet. >>
> >> TIA,
> >> Neil


fisher <fisher_NO Posted on 2009-09-11 05:11:18.0Z
From: fisher <fisher_NO@SPAM_star.wckp.lodz.pl_PLEASE>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.objects
Subject: Re: Compute Moving Average
References: <4aa10f4e$1@forums-1-dub> <4aa9ce6c.7db5.1681692777@sybase.com>
In-Reply-To: <4aa9ce6c.7db5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090910-0, 09/10/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa9dbf6@forums-1-dub>
Date: 10 Sep 2009 22:11:18 -0700
X-Trace: forums-1-dub 1252645878 10.22.241.152 (10 Sep 2009 22:11:18 -0700)
X-Original-Trace: 10 Sep 2009 22:11:18 -0700, vip152.sybase.com
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9890
Article PK: 737021

First of all which DB are you using ?
Oracle for example can deliver this with analytical functions.
DW can access data in different rows... use col[-1] to access value from
previous row.

this would make your formula like :

If ( getrow()> 9,
(col[-9]+col[-8]+col[-7]+col[-6]+col[-5]+col[-4]+col[-3]+col[-2]+col[-1]))/10,0)

BTW you should let us know which version of PB you're using

Neil wrote:
> I think what I need is a little different from this
> formula...
>
> Example:
> In a datawindow, I have a number column col1 with 100 rows
> and I want a computed column like a moving average with the
> LENGTH of 10.
>
> This computed field will start to have a value at row 10,
> which it sum-up col1 from row 1 to row 10 divide by 10...
>
> At row 11, it will summ-up row 2 to row 11, divide by 10.
> At row 12, it will summ-up row 3 to row 12, divide by 10.
> At row 13, it will summ-up row 4 to row 13, divide by 10.
>
> At 100th row, it summ-up from row 91 to row 100 divide by
> 10.
>
> This is what I mean as "moving"... the row keeps moving.
>
>
>
>
>
>
>> It should be getrow() instead of rownum()
>>
>> I'm sorry to much Oracle/PB switching just before weekend
>> starts
>>
>> fisher wrote:
>>> like CumulativeSum()/rownum() expression
>>>
>>>
>>> Neil wrote:
>>>> Hi,
>>>>
>>>> I would like to ask if its possible to have a moving
>> average >> formula in a datawindow? Please give code
>> snippet. >>
>>>> TIA,
>>>> Neil


fisher <fisher_NO Posted on 2009-09-11 16:26:49.0Z
From: fisher <fisher_NO@SPAM_star.wckp.lodz.pl_PLEASE>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.objects
Subject: Re: Compute Moving Average
References: <4aa9dbf6@forums-1-dub> <4aaa21e6.bad.1681692777@sybase.com>
In-Reply-To: <4aaa21e6.bad.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090910-0, 09/10/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aaa7a49$1@forums-1-dub>
Date: 11 Sep 2009 09:26:49 -0700
X-Trace: forums-1-dub 1252686409 10.22.241.152 (11 Sep 2009 09:26:49 -0700)
X-Original-Trace: 11 Sep 2009 09:26:49 -0700, vip152.sybase.com
Lines: 79
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.objects:9894
Article PK: 737023

use then Arthur's solution. create a computed containing amount of rows
you want to process (for this example you can name it cf_rows

an then

sum( if( currentrow - getrow() < cf_rows and currentrow - getrow() >= 0,
ValueToSum, 0 )) / cf_rows?

not tested but worth a trying

You can easily modify value of cf_rows with simple modify.

Neil wrote:
> Thank you sirs for the replies. I am using PB 10.0. @Fisher,
> your formula sir is correct but I can not hard-code it into
> the DW as the 10 is a variable. It can 20, 25 or whatever
> the user wishes. So it needs to be a generic formula. Thanks
> again.
>
>
>> First of all which DB are you using ?
>> Oracle for example can deliver this with analytical
>> functions. DW can access data in different rows... use
>> col[-1] to access value from
>> previous row.
>>
>> this would make your formula like :
>>
>> If ( getrow()> 9,
>> (col[-9]+col[-8]+col[-7]+col[-6]+col[-5]+col[-4]+col[-3]+c
>> ol[-2]+col[-1]))/10,0)
>>
>> BTW you should let us know which version of PB you're
>> using
>>
>>
>> Neil wrote:
>>> I think what I need is a little different from this
>>> formula...
>>>
>>> Example:
>>> In a datawindow, I have a number column col1 with 100
>>> rows and I want a computed column like a moving average
>>> with the LENGTH of 10.
>>>
>>> This computed field will start to have a value at row 10
>>> , which it sum-up col1 from row 1 to row 10 divide by
>>> 10...
>>> At row 11, it will summ-up row 2 to row 11, divide by
>>> 10. At row 12, it will summ-up row 3 to row 12, divide
>>> by 10. At row 13, it will summ-up row 4 to row 13,
>>> divide by 10.
>>> At 100th row, it summ-up from row 91 to row 100 divide
>>> by 10.
>>>
>>> This is what I mean as "moving"... the row keeps moving.
>>>
>>>
>>>
>>>
>>>
>>>
>>>> It should be getrow() instead of rownum()
>>>>
>>>> I'm sorry to much Oracle/PB switching just before
>> weekend >> starts
>>>> fisher wrote:
>>>>> like CumulativeSum()/rownum() expression
>>>>>
>>>>>
>>>>> Neil wrote:
>>>>>> Hi,
>>>>>>
>>>>>> I would like to ask if its possible to have a moving
>>>> average >> formula in a datawindow? Please give code
>>>> snippet. >>
>>>>>> TIA,
>>>>>> Neil