Marek Kalita Posted April 18, 2019 Share Posted April 18, 2019 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 Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted April 22, 2019 Share Posted April 22, 2019 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. Link to comment Share on other sites More sharing options...
Marek Kalita Posted April 23, 2019 Author Share Posted April 23, 2019 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 Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted April 23, 2019 Share Posted April 23, 2019 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... Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now