-
Notifications
You must be signed in to change notification settings - Fork 1.3k
aggregate columns #2954
-
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!
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 2 comments 4 replies
-
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:
- The superstore example has 10k rows in it so this expression conservatively evaluates this expression ~50,000,000 times.
- Expressions are calculated before aggregates.
Beta Was this translation helpful? Give feedback.
All reactions
-
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?
Screenshot 2025年03月13日 at 9 40 15 AM
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
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
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!
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Screenshot 2025年03月27日 at 6 55 52 PM
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.