How add calculated column and rows to cross table

[Marek Kalita]

I have a table (datasection.)

I need to create in Tibco Spotfire a table (results section).

I have to add calculated coulmns Vs Plan and Vs Plan % . I didit as custom expresion.

But how I need to add calculated rows (Prime margin (as Revenue-Costs of sales) PM % as PM/revenue and Contribution Margin as PM-Other costs) and CM% as CM/Revenue.

I can create it in excel Pivot tables as: add calculated field and add calculated items in one pivot.

Best regards

Marek

[Khushboo Rabadia]

You will need to add/duplicate the rows in your dataset to create rows for PM/CM etc. Rows cannot be added on fly in visualization in spotfire.

As Revenue, cost of sales etc. will be values present in different rows of one column, you will need to add in rows in your dataset such that the desired values are present in that column so they can be viewed in cross table.

Eg:

You have 10 rows for each category likeRevenue, Sales, Others so 30 rows. You can insert again new 10 rows for PM and update the value in category column.

[Marek Kalita]

I can do it for PM as amoun (i use sum for agregation). But how about PM in %  - it should be dynamically counted. I cant use sum agregation. for 10 rows revenue i can't add 10 rows for PM% (calculated as sum(PM)/sum(Revenue)). it depends on the layout of the cross table

[Khushboo Rabadia]

Percentage and Number formatting cannot be present in same column. One column can have same set of formatting for all the values. So either you can separate out PM%, CM% in another cross table or convert all the values in String format.

I have attached the dxp showing the approach I mentioned, duplicating rows and then mentioning various calculations you need for each value in a cross table custom expression. I have converted PM%, CM% to String format. This is the closest you can get according to the screenshot shown. You can add custom expression for Vs Plan% as well accordingly.

 

Note: String format will always format the real values to numeric format, it will not show decimal values.

Another approach would be touse jsviz and you canthe design the cross table in your own format through javascripts.

https://community.spotfire.com/wiki/javascript-visualization-framework-jsvi...