getsummary with related break field?

[Reed]

Hi,

I'm trying to generate a report that's reporting a value along with some statistics broken out first by the day of the week, and then by the meal of the day. The table has two relevant summary fields (average and std. dev.) And I have two calc. fields that calculate the relative standard deviation for each of the two break fields.

The calculation for meal type looks like this:

GetSummary ( std_dev ; type )/GetSummary ( average ; type )

The calculation for grand total looks like this:

GetSummary ( std_dev; std_dev  )/GetSummary ( average;average  )

But the calculation for day of the week doesn't work:

GetSummary ( std_dev ; Days::dayOfWeek )/GetSummary ( average ; Days::dayOfWeek )

I guess this is because the break field is a related field (the day of week calc. lives in the day table where the date field is) I can see only two ways to remedy this:

1. Do the day of week calculation in the values table (where it would have to be unstored) This would slow down the sorting and calculations with large numbers of records.

2. Redundantly store the date in both tables so the day of week calculation field can be indexed, which is not a normalized data structure.... maybe this doesn't matter in this case?

Am a really right that you can't use the getsummary function with a related breakfield even though you can create a subsummary part that works when sorted by a related breakfield?

Dana

[mr_vodka]

Am a really right that you can't use the getsummary function with a related breakfield even though you can create a subsummary part that works when sorted by a related breakfield?

Yup. GetSummary does not work with a related break field. Most likely you will have to go with one of your two options.

BTW Happy Birthday, Dana. :party:

[Reed]

Ughhh! I'm officially in my "Mid" 30's

[gdurniak]

Try creating an unstored calculation field, that is equal to your related field Days::dayOfWeek

Then use that instead

[Søren Dyhr]

Indeed!

http://network.datatude.net/viewtopic.php?t=128

so just define the extraneous calc (something we're all too used to doing in FMP)

....when tunneling was of a different nature!

--sd

[Reed]

Defining the unstored calc is of course one option (which I mentioned originally) But in addition to the proliferation of calc fields which (should) be unecessary if GetSummary worked how I expected , the performance of sorting by an unstored calc will suffer with large found sets. Hence, the idea of a lookup or auto-enter to store the date in the values table reduntantly, or even (gasp!) using the date field as the key in the relationship rather than the serial number. (not that I would ever do that)

[comment]

Are you sure sorting by unstored is noticeably slower than sorting by related?

[Reed]

I guess I haven't tested sorting very extensively, but when I've done searches on related fields vs. unstored calcs, the related fields have been much faster. (although I haven't tested this in a simple test file, only in some complex solution where there could be other factors...)

[Søren Dyhr]

Disregarding unindexed entirely is prejudice, not bearing this in mind:

http://network.datatude.net/viewtopic.php?t=102&highlight=debi

--sd

[comment]

I ran a quick test, and it seems that sorting by unstored takes about 14% longer than sorting by related.

However, this wasn't a "clean" test (other applications were running at the same time), so I would await confirmation before reaching a conclusion. And, of course, a served solution may be something else again.

Edited April 1, 2008 by Guest