How to reference "New Column" defined column to define new column？

[hengaini2055]

Feature Request

Description of Problem:

I can define a new column such as sum("Sales") named SalesSum and use in group by datagrid. I want to define a new column reference the defined column SalesSum, the error is "Value error - input column "SalesSum" does not exists".

Potential Solutions:

I think the problem is the "New column" function can only reference the table source column and can not reference "New column" defined virtual column .
If we can reference virtual column defined by "New column", the perspective tool will open a big windows for data analysis.

[texodus]

We used to allow this but removed it because it introduced a lot of UX complexity.

... will open a big windows for data analysis.

Details about your use case would help a lot.

[hengaini2055]

If I want to display a metric as [sum(profit) / sum(Sales)], how to accomplish this in perspective?

[timkpaine]

you need to provide more details. given columns profit and sales you would do sum(“profit”) / sum(“sales”). but if your columns profit and sales are themselves computed columns, then you cannot as mentioned above.

[hengaini2055]

As you mentioned, I would do sum("profit") / sum("Sales"), but the value is wrong.

[hengaini2055]

This picture looks more clear.

[texodus]

sum in expression syntax is not a rollup - you can't rollup within an expression and expressions are calculated before aggregates (which in this case is also sum, but this version is a rollup, calculated last). It is the sum of the vararg inputs, for which you are only providing one.

If we rename the aggregate col_sum to disambiguate, the highlighted value in your screenshot is essentially col_sum(sum("sales", ..) / sum("profit", ...)), which because your expression sum has only one argument is the identity function, so this column is now col_sum("sales" / "profit"). Please review the perspective expression docs which cover evaluation order in detail, and the function references in perspective docs and exprtk docs (which are also visible in the function definition autocomplete dropdown within the component).

[hengaini2055]

Can we add a new calculation layer? That based on the group by or rollup dataset. That will be simple, the new column can reference this dataset column as before. The successful method should do once more.
I am sorry, I don't know the internal mechanism of perspective; but frankly, this requirement is so intuitive.