Logout / Access Other products Drop Down Arrow
Get live help Monday-Friday from 7AM-7PM ET (12PM-12PM GMT)  •  Contact Us
Check out our new FAQ section!
RSS Feed
TitleUsing Data Buffet: Aggregate series by area
AuthorPhillip Thorne
Question

Can I automatically retrieve a concept for a set of related areas, and aggregate them into a single value?

Answer

The short answer

Data Buffet has no mechanism to automatically aggregate separate but related series.  You will need to download then add, or add with a formula before downloading.

The long answer

In Data Buffet baskets, you can use geo wild cards and geo lists to bulk-retrieve multiple series that are related by area.  For example:

  • FETA.RI^^^         = Five series of forecasted employment for all counties in Rhode Island
  • FHHOLDQ.DV_ALL.LST = Nine series of forecasted households in all census divisions

But what if you wanted the sum of employment over the five counties in the state?  Can this be done with wild cards?  Regrettably, no.  You will need to write an explicit basket formula that references each constituent series, for example:

(FETA.RI001+FETA.RI002+FETA.RI003+FETA.RI004+FETA.RI005)

You can do the same thing with a spreadsheet formula after running a basket and downloading its output, but using a basket formula (before downloading) may simplify your workflow.

The obvious next question: What about other kinds of aggregates, such as average, maximum, or standard deviation?  There is no basket formula for these.

Tips

  • A formula must be enclosed with parentheses; this is how the Data Buffet engine recognizes it as such.
  • Don't jump to a single complex formula; rather, develop it by (a) retrieving all constituent series separately, to verify that they exist, and by (b) testing sub-expressions.
  • Formulas can also be used in charts and maps.