Support smarter splitting

[aszenz]

Feature Request

When splitting extract out common sub-columns instead of repeating them for each value.

Description of Problem:

I have this dataset

contract_no	company	month	qty
C-1	ABC Corp	Jan	100
C-1	ABC Corp	Feb	100
C-2	XYZ Inc	Feb	200
C-2	XYZ Inc	Mar	300

I group by contract no, split by month and then aggregate by two cols: unique(company) and unique(qty).

Here's roughly what i see right now in perspective:

,

contract_no	Jan		Feb		Mar
	company	qty	company	qty	company	qty
C-1	ABC Corp	100	ABC Corp	100
C-2			XYZ Inc	200	XYZ Inc	300

Nice but since company name doesn't change per month, the ideal view is:

contract_no	company	Jan	Feb	Mar
C-1	ABC Corp	100	100	0
C-2	XYZ Inc	0	200	300

This problem really becomes worse when u have multiple of these cols (in this dataset, I have infact dozens of columns which don't change per month, repeating them makes the whole split too wide).

Because of this problem I'm forced to almost never use splits in perspective.

Potential Solutions:

1. Create a new aggregation type which forces this behavior, make it a true pivot table
2. Support nested viewers, this would allow the data to be visualized entirely differently, see Nested renderer #2925

[texodus]

Nice but since company name doesn't change per month ...

This is what adding this column as a group_by does already - Perspective can't know this property of your data beforehand. If company is unique to contract_no and you want to collapse the contrat_no parent rows which hav eonly a single child, you can instead group by a composite key for this value as an Expression Column using the concat() function:

{
    "plugin": "Datagrid",
    "group_by": [
        "composite_key"
    ],
    "split_by": [
        "month"
    ],
    "expressions": {
        "composite_key": "concat(\"contract_no\",' ',\"company\")"
    },
    "columns": [
        "qty"
    ]
}

[aszenz]

Thanks for the feedback, the composite key approach doesn't scale to many columns ux wise, since the user doesn't see any column labels.

Perspective can't know this property of your data beforehand

I'm suggesting we could inform it via aggregation type (like unique but for splits instead of group by). So if the company name was actually different then it would just show -.

But since perspective doesn't support any aggregation horizontally (across splits), this is a big change I suppose.

I could do this pivot on the backend, but then I lose the nested headers for each column per month like qty, price since it's just a flat list of columns without any hierarchy.

Overall for me this is the biggest roadblock in moving customers away from excel style pivot reports.

[aszenz]

Another potential solution could be to group by contract no and company and then flatten out rows with only one child, so that contract no and company are shown inline without nesting.