Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #1932 > unrolled thread

calc field to show average of previous set number of records

Back to article view | Back to comp.databases.filemaker

Contents

  calc field to show average of previous set number of records Barbara Fakoury <bfakoury@gmail.com> - 2014-12-10 07:42 -0800
    Re: calc field to show average of previous set number of records Martin Τrautmann <t-usenet@gmx.net> - 2014-12-10 17:16 +0000
    Re: calc field to show average of previous set number of records clkaufmann@gmx.ch (Christoph Kaufmann) - 2014-12-10 19:27 +0100
      Re: calc field to show average of previous set number of records Your Name <YourName@YourISP.com> - 2014-12-11 09:26 +1300
    Re: calc field to show average of previous set number of records Jeremy Bante <jeremy.bante@gmail.com> - 2014-12-10 12:30 -0800

#1932 — calc field to show average of previous set number of records

Hi,

I'm using FMP 9 on a Mac, System 10.6.8 (Snow Leopard).

I searched the forum for this answer but was not able to get anywhere.  

I have a database that is tracking my health info.  Each record is a day, and has a number field for total sleep.

Can a calculation field be created to show the sleep average for the last 2 days, another field to show it for the last 3 days, and another field to show it for the last 7 days?

Right now it's set up with find scripts for the date ranges - but I would prefer to just have calc fields.

Thanks,

Alex

[toc] | [next] | [standalone]

#1933

On Wed, 10 Dec 2014 07:42:38 -0800 (PST), Barbara Fakoury wrote:
>  Can a calculation field be created to show the sleep average for the last 2 days, another field to show it for the last 3 days, and another field to show it for the last 7 days?

yes

>  Right now it's set up with find scripts for the date ranges - but I would prefer to just have calc fields.

You might create a trigger to identify the last 2 days, another one for
the last three days, another one for the summaries. And then you might
takes this as a break field for a summary field.

Personally, I'd avoid extra fields and just run a script to loop through
the records and use variables to compute the average values, then
insert those variables to the fields where I want to store them.

- Martin

[toc] | [prev] | [next] | [standalone]

#1934

Alex wrote:

> Each record is a day, and has a number field for total sleep.
> 
> Can a calculation field be created to show the sleep average for the last
> 2 days, another field to show it for the last 3 days, and another field
> to show it for the last 7 days?

Yup. The calc is quite simple:

avg ( lastsevendays::sleephours )

Lastsevendays is the name of a new occurence of your table. You create a
relation based on

Days                    Lastsevendays     
Date              >=    Date
DateteMinus7_c    <     Date

DateMinus7_c is a new unstored calc field, result type date. It's calc
is date - 7.

-- 
Christoph Kaufmann
http://clk.ch

[toc] | [prev] | [next] | [standalone]

#1935

In article <1lwgdlo.elpbk2rt7gi8N%clkaufmann@gmx.ch>, Christoph
Kaufmann <clkaufmann@gmx.ch> wrote:

> Alex wrote:
> 
> > Each record is a day, and has a number field for total sleep.
> > 
> > Can a calculation field be created to show the sleep average for the last
> > 2 days, another field to show it for the last 3 days, and another field
> > to show it for the last 7 days?
> 
> Yup. The calc is quite simple:
> 
> avg ( lastsevendays::sleephours )
> 
> Lastsevendays is the name of a new occurence of your table. You create a
> relation based on
> 
> Days                    Lastsevendays     
> Date              >=    Date
> DateteMinus7_c    <     Date
> 
> DateMinus7_c is a new unstored calc field, result type date. It's calc
> is date - 7.

That should work, and similar relationships for 2 and 3 days.

Since the relationships only ever have up to seven records, it should
be fast, even on an ancient slow computer (unlike an average over all
records, which would slow down as the number of records grows).

Even easier is don't sleep, then the new average calculation field
simply needs to be:

      AverageSleep = 0

;o)

Helpful Harry  :o)

[toc] | [prev] | [next] | [standalone]

#1936

On Wednesday, December 10, 2014 7:42:39 AM UTC-8, Barbara Fakoury wrote:
> Right now it's set up with find scripts for the date ranges - but I would prefer to just have calc fields.

I recommend Martin's scripted approach over the calculation options. With the scripting, you're in control of when the calculation of the averages happens, and it only happens when the script is running. Almost all the possible calculation-field-based solutions will require unstored calculations, so they will re-evaluate every time you look at them. This can be a performance problem every time you look at a large list of records. This also doesn't make sense as work for the computer that should be necessary, since the historical averages are not likely to change when you look at them later.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.filemaker

csiph-web