differencing aggregates in dataview

[rustyconover]

Hi Perspective Team,

I've been using the Datagrid for a bit and what would be really useful for me is to be able to calculate differences across aggregate columns that are split.

My specific fictional example:

I have a dataset that has pnl per portfolio per day that accrues. The pnl column doesn't start a zero every day.

In the data view I split that dataset by day, but I want to be able to see for each portfolio how much the PnL changed per day. This involves a lag() operator in SQL, but I'm not sure how to express it in the builder. Is this even possible?

I know I could go hack my data source but it is so much easier to use Perspective.

Thanks,

Rusty

[texodus]

I believe what you describe can be implemented using >5.1.0 window columns, something like this in JSON:

{
  "group_by": ["portfolio"],
  "split_by": ["day"],
  "columns: ["pnl", "chg"],
  "aggregates": { "pnl": "last", "chg": "sum" },
  "windows": {
    "chg": { 
      "column": "pnl", 
      "aggregate": "diff",
      "partition_by": ["portfolio"],
      "order_by": ["ts", "asc"] 
    },
  },
}

window columns docs
PR

Also potentially related (which I am currently working on) #3198 #2685

(converting to a discussion)