Nested filtering for iterative process -exponential mapping?

Question

let's begin with the simple form. Imagine you have a simple dataset like this one: you want to retrieve the cumulative amounts for each asset.

First I would filter (arrange) the array by the asset value

  var pairs   = ["eur", "usd", "pop", "dot", "cad", "sol"];
  for(i=0; i<pairs.length; i++){
  var filtArray1  = dataArray.filter(function filt(el){return el.asset === pairs[i];});  
    filtArrays.push(filtArray1);

and then perform any sort of operations, like summing up for istance:

  var values  = Object.keys(filtArrays[i]).map(function(e){return filtArrays[i][e].amount});
  parziali.push(values);

  var somma   = values.reduce(function(acc, val) { return acc + val; }, 0);
    somme.push(somma); 

//result "somme": [9.0, 9.0, 6.0, 6.0, 9.0, 3.0]
}

ok, you can use indexOf(); or any other faster method but that's not the point.

The point is: imagine we add an extra layer to this data set now, some date field. Now we have:

and, as for before, you want to be able to retrieve the amount for each asset for each week(those are week numbers of the year)..
How do you do that?
It has suddenly become exponential. What type of process can you use to keep it iterative (i.e.: automatic) and light on work-load at the same time?

You could even want to add an additional layer of data at this point..

as you can see we have two different wallets, both holding euros, but in the same week.
Now we want to be able to retrieve the amount of each asset for each week AND for each wallet. As I said, it kind of becomes exponential: how do you approach that? thanks

ps: the data was obviously previously treated with

  for(i=0; i<data.length; i++){
    var dataRow = data[i];
    var record = {};
    record['weeks']    = dataRow[0];
    record['asset']    = dataRow[1];
    record['amount']   = dataRow[2];
    dataArray.push(record);}  

Answer 1

The problem, as I understand it, is not exponential. The complexity is still linear, one is simply adding more criteria when scanning through the available data.

The technique is to accumulate the data based on the fields that you're trying to group to achieve the subtotals. For example, if you want to group by year/month, then all that is needed is the year/month to define the subtotal buckets. Eg, you will have a bucket for '20181', '20182', '20183', etc. As you loop through the entries in the array, you will use the year/month to identify the bucket and to add the entries value into that subtotal bucket.

If you are including additional fields as part of the subtotal grouping (eg, currency and year/month), then it is simply a matter of adjusting the bucket names to include both currency and year/month. Ie, your subtotal buckets will now be '~cad~20181~', '~cad~20182~', '~eur~20181~', etc. This uniquely identifies the subtotal buckets by currency and year/month. Then, as before, when looping through the entries in the array, you are taking the values from the array entry to identify the currency and year/month bucket that the value belongs... Note that the tildes are arbitrary delimiters to separate the field values when constructing the subtotal bucket names.

a = [
  {geo:"cad", ym:20182, value:3},
  {geo:"eur", ym:20181, value:1},
  {geo:"pop", ym:20182, value:2},
  {geo:"usd", ym:20181, value:3},
  {geo:"cad", ym:20182, value:3},
  {geo:"sol", ym:20181, value:1},
  {geo:"cad", ym:20181, value:3},
  {geo:"pop", ym:20182, value:2},
  {geo:"pop", ym:20181, value:5}
];

 var result = a.reduce( (totals, entry) => {
   let key = '~' + entry.geo + '~' + entry.ym + '~';
   totals[key] = ( totals[key] || 0 ) + entry.value;
   return totals;
 }, {} );


console.log( result );

Variation of the code using a for loop.

arr = [
  {geo:"cad", ym:20182, value:3},
  {geo:"eur", ym:20181, value:1},
  {geo:"pop", ym:20182, value:2},
  {geo:"usd", ym:20181, value:3},
  {geo:"cad", ym:20182, value:3},
  {geo:"sol", ym:20181, value:1},
  {geo:"cad", ym:20181, value:3},
  {geo:"pop", ym:20182, value:2},
  {geo:"pop", ym:20181, value:5}
];

result = {};

// Loop through each entry in arr

for ( let i = 0; i < arr.length; i++ ) {
  
  // Create the subtotal bucket name based on the fields
  // defining the grouping.

  let key = '~' + arr[ i ].geo + '~' + arr[ i ].ym + '~';
  
  // Now that we know which subtotal bucket arr[i].value
  // belongs, let's add it to the bucket.
  result[ key ] = (result[ key ] || 0 ) + arr[ i ].value ;

}

console.log( result );

In both examples, I've collected the values into an object with each property representing a subtotal bucket. A Map object can be used as well, but is less clear in exemplifying the critical concept of naming the subtotal buckets according to how the subtotals are to be grouped...

Hope this helps.

Answer 2

As suggested in the comments, you can use a simple query:

E1:

=query(A:D,"select A,B,C,sum(D) where A is not null group by A,B,C ",1)