CODEWITHSUNDEEP
spotfire
cookiemnstr247
cookiemnstr247

Reputation: 131

Spotfire Cross Table Calculation

I have a table that I am trying to perform a series of calculations on while allowing the underlying data to be filtered to update the values.

Here are some sample values:

Contract    Approver    Analyst
1   Matt    John
2   Matt    John
3   Matt    John
4   Matt    John
5   Matt    John
6   Matt    John
7   Matt    John
8   Matt    Robert
9   Matt    Kim
10  Matt    Jack
11  Matt    Sue
12  Matt    Regina
13  Matt    Robert
14  Matt    Robert
15  Matt    Robert
16  Matt    Robert
17  Matt    Robert
18  Matt    Robert
19  Matt    Robert
20  Matt    Robert
21  Matt    Robert
22  Matt    Jack
23  Matt    Sue
24  Matt    Regina
25  Matt    John
26  Matt    Robert
27  Matt    Kim

I want my resulting table to have the following columns:

    Approver_AnalystIdentifier  CountApprover_Analyst  CountApproverTotal    Percentage(Countapprover_analyst/CountApproverTotal)
    MattJack    2        26       7%
    MattJohn    8        26       7%
    MattKim     1        26       7%
    MattRegina  2        26       7%
    MattRobert  11        26       7%
    MattSue     2       26       7%

How can I do this in spotfire, what visualization should I use and are there any custom expressions I would have to input?

Thanks!

Upvotes: 0

Views: 2970

Answers (2)

user13647553
user13647553

Reputation:

you can use a summary table to do what you want. You can add columns with aggregations and select many columns to be displayed by default on the table.

Upvotes: 0

phiver
phiver

Reputation: 23608

You can pivot your data. Using your example, something like this image. You'll end up with a data table with 3 columns, Approver, Analyst, count(contract). To get your the percentage insert calculated column or custom expression with the formula: [Count(Contract)] / Sum([Count(Contract)]). format as a percentage. The Countapprover total you want is just sum([count(contract)]. If you have more than 1 approver, you will need to use an OVER statement. sum([Count(contract)] OVER ([Approver].

enter image description here

Upvotes: 3

Related Questions