Assume you import data from a Table source of the following format.
<< GeneralUtilities`; fields = {"Country", "Region", "BU", "Year",
"Date", "Sales"};
organization = {{"Argentina", "LATAM", "Americas"}, {"SouthAfrica",
"Africa", "EAME"}, {"Brazil", "LATAM", "Americas"}, {"Japan",
"Japan", "APAC"}, {"Australia", "ASEAN", "APAC"}, {"Germany",
"Europe", "EAME"}};
SeedRandom[0];
list = Flatten[
Table[Join[
organization[[i]], {year, DateObject[{year, month, 1}],
RandomInteger[{100, 1000}]}], {i, 6}, {year, 2004,
2013}, {month, 1, 6, 5}], 2];
sales = Dataset[AssociationThread[fields, #] & /@ list]
I would like to summarize the data at the year level. If working with a database, an SQL command of the following format would allow you to create a dataset that is still flat.
SELECT sales.Country, sales.Region, sales.BU, sales.Year, Sum(sales.Sales) AS SumOfSales FROM sales GROUP BY sales.Country, sales.Region, sales.BU, sales.Year;
Using
sales[GroupBy@Key["Country"], GroupBy@Key["Region"],
GroupBy@Key["BU"], GroupBy[Key["Year"]], Total, "Sales"]
Creates a multilevel hierarchical data structure, which is not as simple to operate as a table type of dataset.
Is there a way to operate (total,mean, median,etc) on a dataset by grouping on several keys of interest while keeping the dataset flat the same way as done with the SQL procedure?