Running Totals/ nth most frequent by group issue

[agray123]

The dataset below (believe me - there are many more) needs to be summarized with the 6 most common competencies per jobcode - I have 6 distinct fields of data - and there is a great deal of variation in how each row is sorted (not all ascending or descending).

How do I pull this off?

And if I can com up with a series -of 6 common competencies per jobcode - how can I then compare that with each individual set to determine if there are groups of employees with varying percentages of difference from the "norm"?


Comp 1 Comp 2 Comp 3 Comp 4 Comp 5 Comp 6

00150 Executive Secretary 1
2 3 5 11 15 17
2 6 11 14 15 17
00160 Executive Secretary 2
15 11 30 2 18 34
2 11 15 17 18 20
2 5 6 11 15 17
2 15 20 11 34 46
2 5 14 15 18 20
15 42 5 17 30 46
0023EX Clerk Typist 3 - Exempt
2 5 11 15 18 20
01010 Management Analyst 1
2 5 11 15 30 32
2 7 14 15 32 33
2 7 14 15 32 33
2 7 14 15 32 33
2 10 15 24 28 32
5 7 11 12 14 18
10 2 1 32 14 34
2 7 15 19 32 33
2 9 11 15 18 32
9 11 15 18 30 32

 

[MisterMo]

1) create a Group and set it by Job Code 00150 , 00160,...

2) create the a running totals for each competency,

3)on the top part of each panel choose the {table.Comp} and select count as type of summary

4)Middle part evaluate each competency eg. comp1 or comp2 here you should use the formula option
{table.Comp}='Comp1'

5) reset on change group


this should do it.




Mo

 

[MisterMo]

Oh I forgot

place all the running totals on the group footer
and then suppress the detail section

all the best

Mo

 

[agray123]

here is the issue - I want to see the frequesnmcyt of say the number 2 - across each of the 6 competencies - and have that count appear in the group header - the ability to be able to sort these totals in a descending order would be great - i should probably try this with SPSS.

 

[MisterMo]

what do you mean?

You want to see the values on the detail section as well as the running totals?

Mo

 

[agray123]

FOR THIS SET:

00160 Executive Secretary 2
15 11 30 2 18 34
2 11 15 17 18 20
2 5 6 11 15 17
2 15 20 11 34 46
2 5 14 15 18 20
15 42 5 17 30 46

I want to see a frequency distribution for all groups:

2 - 4
15 - 6
11 - 4
18 - 3

and so on - not sure if this is possible - but I would like it to sort in the most frequent down.


Then I can do a formula to return me the 6 most frequent./

 

[MisterMo]

I am sure you can get the 6 most frequent one and you can set them in whatever order you want them, I just don't think you can get a count for them

I need to the how the raw data looks like

Mo

 

[lbass]

You could create a union all query that combines all fields into one field, as in:

Select
comp1, jobcode, jobdesc
From
Table
Union all
Select
comp2, jobcode, jobdesc
From
Table
Union all
Select
comp3,jobcode, jobdesc
From
Table
Union all
Select
comp4, jobcode, jobdesc
From
Table
Union All
Select
comp5, jobcode, jobdesc
From
Table
Union All
Select
comp6, jobcode, jobdesc
From
Table

Since in 8.0 you cannot alter the initial select, you would need to first add Comp1 and the jobcode and jobdesc to your report canvas. Then you would go into the Show Sql query and modify the SQL there by adding Union All and copying and pasting the initial select, changing the field each time.

In 9.0 and above, you could use the "Add Command" as your datasource, and create the entire query there.

Then you can group on {table.comp1} and insert a summary (count) on the results. You should then be able to do a topN/group sort on the results.

-LB