How do I get the Average of a Unique Count in a Cross Table

[Thomas Love]

I have a simple set of data that I want to know the average of items worked.

Example:

 

 

 

Worker

Widget ID

Year

 

 

A

1

2018

 

 

A

1

2018

 

 

A

1

2018

 

 

B

2

2018

 

 

B

2

2018

 

 

B

2

2018

 

 

C

3

2018

 

 

C

3

2018

 

 

C

3

2018

 

 

C

3

2018

 

 

A

4

2018

 

 

A

5

2018

 

 

A

6

2018

 

 

A

7

2018

 

 

A

8

2018

 

 

A

9

2018

 

 

A

10

2018

 

 

A

11

2018

 

 

A

12

2018

 

 

B

13

2018

 

 

B

14

2018

 

 

B

15

2018

 

 

B

16

2018

 

 

B

17

2018

 

 

B

18

2018

 

 

B

19

2018

 

 

B

20

2018

 

 

 

 

Etc..

The spotfire unique count for the sample data looks like this:

Worker Unique Count

A 114

B 19

C 63

What I want to know is what is the average number of widgets the worker worked. I know worker B worked 19 in 2018 but what is their average worked for 2018. Worker B's average would be 1.58 per month.

Is there a way to do this in Spotfire

[Colin Gray 3]

Hi there,

 

It depends if you want to do this as a calculated column, or part of a cross table. If you want to do it as a calculated column you can use this expression:

 

UniqueCount([Widget ID]) Over ([Worker], [Year]) / 12

 

If using a cross table, set your vertical axis to be Worker and Year, then set your cell value to be the expression:

 

UniqueCount([Widget ID]) / 12

 

and give it a name.

 

Hope this helps,

Colin