Calculate percentages in Spotfire 2.1.0

TIBCO Spotfire Community » TIBCO Spotfire » TIBCO Spotfire Clients » Calculate percentages in Spotfire 2.1.0

Last post Tue, Apr 5 2011 4:23 AM by Taher. 4 replies.

Page 1 of 1 (5 items)
	Sort Posts: Previous Next

Thu, Jul 29 2010 11:13 AM

othonas
Joined on Thu, Jul 29 2010
Posts 3

Calculate percentages in Spotfire 2.1.0

Reply Contact

Hello,

I am working on Spotfire 2.1.0 and trying insert a column next to the Count column (please see table below) that calculates what percentage each variable is relative to the total. Can you please help me?

Thank you in advance

Variable Count

Variable 1         12
Variable 2           6
Variable 3           3
Variable 4         19
Variable 5        1398

Total 1438

Filed under: table

Fri, Jul 30 2010 9:49 AM In reply to

Christof
Joined on Mon, Feb 18 2008
Germany
Posts 1,081

Re: Calculate percentages in Spotfire 2.1.0

Reply Contact

Hi,

try this expression for a new column

sum([Count]) over [Variable] / sum([Count])

Variable	Count	sum([Count]) over [Variable] / sum([Count])
Variable 1	12	0.83 %
Variable 2	6	0.42 %
Variable 3	3	0.21 %
Variable 4	19	1.32 %
Variable 5	1398	97.22 %

Format new column as Percent to display it like here.

Christof

-----------------------------------
Dr. Christof Gaenzler
Sr Solutions Consultant
TIBCO Spotfire

Filed under: over

Fri, Jul 30 2010 12:23 PM In reply to

othonas
Joined on Thu, Jul 29 2010
Posts 3

Re: Calculate percentages in Spotfire 2.1.0

Reply Contact

Hello,

Thank you for your email. Here is the problem I have

The count mentioned above refers to a total count of observations (per variable). Those 5 variables are 5 different categories of a single variable. I tried the coding but didn't work. Can you please help me?

Thank you

Here is the table I am working on:

OpRisk Lvl1 Count([Event ID])

Business Disruption and Systems Failure                    12
Clients, Product & Business Practices                         6
Damage to Physical Assets                                        3
Employment Practices and Workplace Safety              19
Execution, Delivery and Process Management              1398

Grand total 1438

Sat, Jul 31 2010 8:59 AM In reply to

Christof
Joined on Mon, Feb 18 2008
Germany
Posts 1,081

Re: Calculate percentages in Spotfire 2.1.0

Reply Contact

Okay, so you are doing this inside a Cross Table visualization, which automatically pivots the data. You can still use my expression to create a new column, or you can pivot the data to do this calcalutaion.

Doing a sum of counts is doable, e.g. add a new calculated column with the the number 1 as integer column. Then you can use the sum of this new column to do the count-calculation.

There are lots of other ways how to use the custom expressions inside a CrossTable to do what you want to achieve, just do some gymnastics with the custom expressions inside the CrossTable.

Christof

-----------------------------------
Dr. Christof Gaenzler
Sr Solutions Consultant
TIBCO Spotfire

Filed under: custom expression

Tue, Apr 5 2011 4:23 AM In reply to

Taher
Joined on Mon, Apr 4 2011
Posts 14

Re: Calculate percentages in Spotfire 2.1.0

Reply Contact

Hi Christof,

I am interested to know the solution to percentage computations in cross tab or any other way. I am new to Spotfire, I have read these posts several times but could not get it exactly.

How to display values (values can be summed up) as percentage of columnar totals OR row grand totals in a cross tab ? So the questions are 1) Is this is supported in a crosstab ? 2) if not what is the other way and the detailed steps/materials to do it ? Can you please help ? Thanks-Taher

Page 1 of 1 (5 items)

Spotfire's interactive information visualization and analytic solutions give users a remarkable experience for quickly and easily querying data and reporting results for superior business intelligence. From portfolio management and customer retention programs to key processes such as CRM, marketing, research, bioinformatics, yield and asset management and design for manufacturing, enterprises around the world rely on Spotfire's business analytics software to improve operational performance.