AnsweredAssumed Answered

Help with calculation: Sum of a found set without performing the find

Question asked by chris_vfx_la on Feb 13, 2010
Latest reply on Feb 15, 2010 by comment_1

Title

Help with calculation: Sum of a found set without performing the find

Post

Hello,

I am trying to obtain the sum of specific records without performing a find for those records. 

My database tracks the progress of cabinets being produced in three different facilities. 

One cabinet = 1 record. 

I have a field called : "WOOD SHOP LOCATION" with a drop down of three options: Los Angeles, Chicago, New York

I have another field called: "STATUS" with a drop down of three options: Not Started, In progress, Delivered

My goal is to have ONE layout (a breakdown) that contains fields that calculates the following running totals:

LOS ANGELES:
Not Started: [__X__]  - the number that appears in this field is the sum of the found set of (Wood Shop Location: "Los Angeles") and (Status: "Not Started")

In Progress: [__X__] - the number that appears in this field is the sum of the found set of (Wood Shop Location: "Los Angeles") and (Status: "In Progress")

Delivered [__X__] - the number that appears in this field is the sum of the found set of (Wood Shop Location: "Los Angeles") and (Status: "In Progress")

The same type of fields would appear for CHICAGO and NEW YORK.

I know that I can do an OVERALL calculation (of all records) of "Not Started," "Work in Progress" and "Delivered" by using this type of formula:


ValueCount ( FilterValues( List (Office Location ); "Los Angeles"); "In Progress")) 

- the result of this is a '1' if the combination appears, '0' if it doesn't.  Then, I create another field that SUMs these totals.

Please let me know if you have any ideas.  Any thoughts are greatly appreciated.

-Chris






 












Outcomes