aggregate columns

[vera-atomic]

Trying to make sense of how I could use expressions to create a custom aggregate cumulative sum column for each of my split_by pivot columns.
I've seen the issue opened here: #2624
And I tried with an example from the library that doesn't have live data:
https://perspective.finos.org/block/?example=editable

This is the starting config object:

{
  "version": "3.4.0",
  "plugin": "Datagrid",
  "plugin_config": {
    "columns": {},
    "edit_mode": "EDIT",
    "scroll_lock": false
  },
  "columns_config": {},
  "settings": true,
  "theme": "Pro Light",
  "title": null,
  "group_by": [
    "Segment"
  ],
  "split_by": [
    "Ship Date"
  ],
  "columns": [
    "Quantity"
  ],
  "filter": [],
  "sort": [],
  "expressions": {},
  "aggregates": {}
}

Then I tried adding the code you've suggested in issue #2624
But adapting it to this example

var cum_quantity := 0;
for (var idx := 0; idx < index(); idx += 1) {
    cum_quantity += vlookup('Quantity', idx);
};

cum_quantity

here's the config post setting up the new column:

{
  "version": "3.4.0",
  "plugin": "Datagrid",
  "plugin_config": {
    "columns": {},
    "edit_mode": "EDIT",
    "scroll_lock": false
  },
  "columns_config": {},
  "settings": true,
  "theme": "Pro Light",
  "title": null,
  "group_by": [
    "Segment"
  ],
  "split_by": [
    "Ship Date"
  ],
  "columns": [
    "Quantity",
    "New Column 1"
  ],
  "filter": [],
  "sort": [],
  "expressions": {
    "New Column 1": "var cum_quantity := 0;\nfor (var idx := 0; idx < index(); idx += 1) {\n    cum_quantity += vlookup('Quantity', idx);\n};\n\ncum_quantity"
  },
  "aggregates": {}
}

But the new column is showing 0 everywhere instead of cumulative sum.
I would have expected the totals row, for first split by and new column 1 to show 5, then for second split by column and new column 1 to show 5 + 11 = 16 etc for subrows too

What am I doing wrong here please?
TIA!

[texodus]

Your expression does not work because the "editable" example does not set an index, and the implicit index type is "integer" (#2624 talks about this distinction in the paragraph following the code snippet you copied):

var cum_quantity := 0;
for (var idx := 0; integer(idx) < index(); idx += 1) {
    cum_quantity +=  vlookup('Quantity', integer(idx)); 
};

cum_quantity

Note:

1. The superstore example has 10k rows in it so this expression conservatively evaluates this expression ~50,000,000 times.
2. Expressions are calculated before aggregates.

[vera-atomic]

How do i know when a pivot is indexed or not?
Does this mean that if I set the "editable" example to "READ_ONLY" it should work?
I just tried and it gives me a result I dont understand, is this because my quantity column is a sum?

This is the code in new column 1:

var cum_quantity := 0;
for (var idx := 0; integer(idx) < index(); idx += 1) {
    cum_quantity +=  vlookup('Quantity', integer(idx)); 
};

cum_quantity

I'm not sure then how to setup my table if in my case the first column is a sum aggregate, and i want totals of this column, for each split_by. So first row here (total) would be: 5, then 16, etc...

Honestly was just looking for a row totals functionality, but it seemed like that's not implemented in the library? So thought a custom running totals column per split_by was the way to go, but finding it hard to understand how to implement with expressions.
Is there maybe an example where it's easier to setup in the perspective list of examples? Also, I do have a really large dataset, more than 10k rows...

TIA! Appreciate your insight

[texodus]

How do i know when a pivot is indexed or not?

By referring to the docs on index from the user guide.

Does this mean that if I set the "editable" example to "READ_ONLY" it should work?

I have absolutely no idea what you're talking about. What does changing the edit mode of this example have to do with your issue?

I'm not sure then how to setup my table if in my case the first column is a sum aggregate, and i want totals of this column, for each split_by. So first row here (total) would be: 5, then 16, etc...

"totals" is not a method of summarizing a column, this is why you must choose an aggregate. You want sum, which you've chosen, these cells are the column sums (for e.q. Quantity):

Is there maybe an example where it's easier to setup in the perspective list of examples? Also, I do have a really large dataset, more than 10k rows...

Then calculating this value with factorial complexity is probably not a viable workaround. Do you only want the totals, or so you want the cumulative sums?! These are wildly different things.

[vera-atomic]

Hmm, I realize I might be mixing some things up here. So, I want, if we look at screenshot, a result that looks like this:

                      | 2014-01-07  | 2014-01-08 |
                      | qt. | cs.   | qt.  | cs. 
TOTAL.                | 5.  | 5.    | 11.  | 16 
Consumer              | 2.  | 2.    | 3.   | 5
Corporate             | 3   | 3.    | 0.   | 3
Home Office           | 0   | 0.    | 8.   | 8 

So that's cummulative sums for each split_by column, for each of the rows and aggregated rows

Alternatively, would also just be very happy to show one summary row total column at the very end. But didn't see an easy way to do this. So that's a table like this:

                      | 2014-01-07  | 2014-01-08 | TOTAL
                      | qt.         | qt.        |  qt.
TOTAL.                | 5.          | 11.        | 16 
Consumer              | 2.          | 3.         | 5
Corporate             | 3           | 0.         | 3
Home Office           | 0           | 8.         | 8 

How could I achieve this please knowing I'll have datasets of 10k+ items that will be aggregated? Thanks!

[neal-atomic]

Thanks @texodus - appreciate your follow-up. Adding to this with an example use of your pivot. We're looking for the simplest way to add the Total (drawn in) for the rows on the far right. Would love any guidance on the simplest way to activate that.

[neal-atomic]

Hi @texodus - checking back in. We're getting asked about this a lot, and it's hard for us to figure out the solution here.

Can you advise on the best way to take a pivot view and get a sum total for the rows at the end? Is it possible in Perspective?

Thank you.