Restricted Statistics?

[LaRetta]

I have table with several Processes (actions I must time). I store the action times in a Log table. I want to display the average time each process takes. I know how to use summary in Log table but I want to restrict the relationship so it only averages the last 20 Logs (per Process). I want it restricted because 1) we don't care how long it USED to take; we just want an idea of how long it takes now and 2) I want to keep the speed fast.

1) I don't know how to restrict the relationship to only 20 log records.

2) If a Process breaks (indicated by no End Time), then I want it skipped and not counted as part of the 20 good prior actions to average.

I've attached a file with the basics. Can I get some direction on restricting by quantity and omitting broken actions? I would sure appreciate the assistance.

LaRetta

Last20Average.zip

[comment]

Getting the last n related values is easy (in version 8.5 and higher) by:

RightValues ( List ( Related::Value ) ; n )

Since you want only unbroken actions, you will need to add filtered relationships, based on say:

Case ( Begin and End ; ProcessID )

Now, you could get 20 IDs and base yet another relationship on those, so you could use Average() over the relationship. However, since you're only talking about 20, you could grab the actual values and average them in a custom function. For this, it would be probably easier if the initial values were numbers, not times (you could always convert the result to time).

Edited September 9, 2007 by Guest
fixed an omission in the first formula

[comment]

Uhm... forgot to mention another option:

A custom function using GetNthRecord() could also get last 20 valid values, without the need for a filtered relationship. It would need to start at the end, though, i.e. at Count ( related::ID ), and work backwards until it gathered 20, otherwise it would get very slow with a large set.

[LaRetta]

1) Management needs to view these timestamps and 2) I already have an existing relationship. The less I have to modify/add to this process, the better.

So yes, I'm interested in CF. Ideally, we could put in the number of related records (in case we need to extend it to 30 or so, for a full month perspective). But I don't ever expect more than that. How can I best pull this all together then? Should I log the timestamp but GetAsNumber() in a CF? And would the CF be GetNthRecord() or ? I don't quite see what you are seeing.

And yes, doh, I thought about using another field for the ProcessID if End was empty ... right before you posted. Can you say a bit more so I can nail this puppy? Thank you so much! :wink2:

Edited September 8, 2007 by Guest

[LaRetta]

Oh! I'm always happy to change my process to match the need or suggestions made! I just meant that I couldn't see how to display a date/time if I'm logging numbers without another calculation (since field format wouldn't let me display number as timestamp). I wasn't sure which way to go! Nor am I hesitant in it taking more effort - I just want it as lean as possible all-around; as I'm sure you understand as well! :^)

Edited September 8, 2007 by Guest

[comment]

I think you could it all in a single calc field and one custom function. But could you provide more sample data? It's hard to test when a process has no more than 3 log records...

[LaRetta]

Okay, here's a REV with 150. If this isn't enough, I think you can run the New Records script and get 150 more random records. I have no prior process which logs the times (it has the start times but not the end times). And there are so many breaks and it crosses many days (and it's vs. 5.5) and dates/times are in different fields and dates weren't logged if same day.

I hope this is sufficient; if not let me know and I'll take it further.

FILE REMOVED. SEE DATA FILE BELOW.

Edited September 9, 2007 by Guest

[comment]

Well, never mind - I have finished it already, so I'll leave the testing to you.

Last20Average.fp7.zip

[LaRetta]

I imported my records. And the display is instant as well, even when I changed it to 30! It is perfect. It will also be fun figuring out your CF. Thank you so much, Michael! And I won't need to add another restricted relationship at all. This tickles me!

[comment]

30 is not a lot, but the nice thing about it is the speed should be about the same whether it's 30 out of 150, or 30 out of 150k. Just take into account I whipped this out very quickly, so look carefully for mistakes.

[LaRetta]

The thing I particularly like about this, Michael, is the fact that I don't have to create ANOTHER table occurrence. It is amazing how many tiny TOs we end up creating just to filter and provide this type of information. In a prior solution I finished this spring, a CF could replace easily 30 TOs in an already overflowing graph. I like this very much and it will come in handy!

I'll be implementing it Monday and then running it through the week - importing many times a day for testing. I'll let you know if there is a problem with the implementation that I haven't considered or with something in the syntax (although I have grave doubts of that happening)!

Edited September 9, 2007 by Guest

[comment]

One thing I have noticed already: your relationship is sorted, and this could skew the results away from what you want. The CF follows the relationship's sort order, so 'last n' is interpreted in that context.

[comment]

OK, a little bug. The CF needs to modified to deal with cases where the last-processed record doesn't have an end time:

Let ( [

valid = GetNthRecord ( startField ; countRelated ) and GetNthRecord ( endField ; countRelated )

] ;

Case ( valid ; GetAsNumber ( GetNthRecord ( durationField ; countRelated ) ) )

&

Case ( n > 1 or not valid ; Case ( valid ; ¶ ) & GetLastNvalidTimes ( startField ; endField ; durationField ; countRelated - 1 ; n - valid ) )

)

[LaRetta]

Mine kept breaking. I realized it was because the data I used was random generated and was not in time sequence so the results were incorrect. I also was impressed that, with correct sequential time data and 4,000 records, it remained VERY fast even when requesting the last 500 records.

Here is a file with 4,000 time-sequential records showing the speed.

Michael, can you explain your thinking in coming up with this solution? I mean, how you stepped through the logic? BTW, I moved it again to left brain because it didn't look right in CF section.

BTW, why do I keep moving it? Because I'm a woman. :girlgiggle:

Last20AverageDATA.zip

Edited September 9, 2007 by Guest

[comment]

can you explain your thinking

I can try, but I don't always follow it myself.... Anyway, at first I thought I am going to need TWO additional TO's: one to filter only valid (completed) entries, the other to filter the last n records of those. Each would also require a calculation field. But this was the only way I could use Average() over a relationship.

Then I thought, what if the second calculation listed n durations directly, instead of just the ID's? Obviously, there would a problem of how to average a list of times. I could add another duration calculation, this time with a number result, or... why not make a custom function that averages times?

One way to make such function would be to convert, recursively, each individual value to a number. The breakthrough happened here, as it occured to me that if the CF goes over each individual value, it can just as well go over the values in the existing TO.

IOW, if we're not going to use the built-in Average() function, there's no point in struggling to get a precisely filtered relationship. The CF can do almost all of the work: test for valid entry, convert it to a number and count.

[LaRetta]

But this was the only way I could use Average() over a relationship.

Ah. I stopped there. I decided I needed another relationship. And I'd need Average() based upon filter. This doesn't mean that I could have come up with anything else. But it tells me that I stopped too soon in my thinking.

Thank you so much! :beertime:

Edited September 9, 2007 by Guest