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.