Grouping a Pivot Table

[PhilMyWallet]

I currently have a pivot table showing the agent names(Rows) the skills they have (Columns) and the level of skill they are on (Data).

There are some agents which all have the same skills & levels.

Is there a way I can group these together on a pivot table.

I use v.8.5


Cheers



Phil

 

[BobSuruncle]

You might try inserting the skill level as a second column.
I'm assuming this is a numeric value??(you didn't say), so you'd likely want to do a Specified Order on that group in the crosstab, using ranges to organize the groupings (1 to 3, 4 to 6, etc.)


Bob Suruncle

 

[PhilMyWallet]

Yes. The Skill level is a number. I still have a challenge with the grouping. here is the scenario:

Ten staff have 2 skills (Sales = level 10, Customer Service = level 5). These agents are classed as 'Sales agents'

Another 10 staff have the same skills, however they have different levels (Sales = level 1, Customer Service = level 2. These agents are classed as 'Customer Service agents'.

How do I show a cross tab which identifies all sales agents & all customer service agents seperately.

Does this make sense?


Regards


Phil

 

[BobSuruncle]

Is there a database field that distinguishes between Sales Agents and Customer Service agents?
If so, you could use that field as the first group in the Column section of the Crosstab expert.


Bob Suruncle

 

[lbass]

It would really help to see some sample data. If the level values are always 10 and 5 for Sales Agents, and 1 and 2 for Customer Service Agents, then you could try creating a SQL expression {%level}:

(Select max(A.`level`) from table A where
A.`name` = table.`name`)

Replace "table" with your table name, and replace "level" and "name" with your exact field names. Leave "A" as is, to act as an alias table name. Then create a formula:

if {%level} = 10 then "Sales Agent" else
"Customer Service Agent"

Insert this as your highest level row in the crosstab.

-LB

 

[PhilMyWallet]

I have shown some example data below, however it is an export from excel, so it will need copying & pasting back into excel to show the correct format.

Sales1 Callbacks Sales2 Sales 3 Sales4 Special Sales1 Support PROMOTIONS Sales5 Sales 6 Sales7
Agent Name1 -- 1234 30 40 48 0 10 10 48 0 30 30 40
Agent Name2 -- 2345 30 40 48 0 10 10 48 0 30 30 40
Agent Name3 -- 3456 30 40 40 0 20 20 48 10 30 30 40
Agent Name4 -- 4567 40 40 48 0 10 10 48 0 30 30 40
Agent Name5 -- 5678 30 40 48 0 10 10 48 0 30 30 40
Agent Name6 -- 6789 30 40 48 0 10 10 48 0 30 30 40
Agent Name7 -- 1234 40 30 0 48 20 0 0 10 30 30 40
Agent Name8 -- 1234 40 40 0 20 0 0 0 0 0 0 0
Agent Name9 -- 1234 30 40 40 0 20 20 48 10 30 30 40


Agent 1, Agent 2, Agent 4, Agent 5 & Agent 6 All have the same skills & same levels. These are classed as Knowledge level 1' agents.

Agent 3 & Agent 9 both have the same skills & levels. These are classed as 'Knowledge level 2' agents

Agent 7 is classed as 'Knowledge level 3'

Agent 8 is classed as 'Knowledge level 4'

See below how I want my crosstab to show :

Knowledge Level 1 Knowledge Level 2 Knowledge Level 3 Knowledge Level 4
Agent Name1 -- 1234 YES
Agent Name2 -- 2345 YES
Agent Name3 -- 3456 YES
Agent Name4 -- 4567 YES
Agent Name5 -- 5678 YES
Agent Name6 -- 6789 YES
Agent Name7 -- 1234 YES
Agent Name8 -- 1234 YES
Agent Name9 -- 1234 YES


Again the above is an export from excel so needs copying & pasting.

Your help on this one is really appreciated !!!


Phil

 

[lbass]

Your second post changes the approach, since it shows that skills are not instances of one field.

You haven't answered the question of whether the particular values are always the same per knowledge level. Let's assume they are. Then I think you could create a formula like {@level}:

stringvar x := totext({table.sales1})+","+totext({table.callbacks})+","+totext({table.sales2})+","+totext({table.sales3})+","+totext({table.sales4})+","+totext({table.specialsales1})+","+totext({table.support})+","+totext({table.promotions})+","+totext({table.sales5})+","+totext({table.sales6})+","+totext({table.sales6});

if x = "30,40,48,0,10,10,48,0,30,30,40" then
"Knowledge Level 1" else
if x = "30,40,40,0,20,20,48,10,30,30,40" then
"Knowledge Level 2" else
if x := "40,30,0,48,20,0,0,10,30,30,40" then
"Knowledge Level 3" else
if x = "40,40,0,20,0,0,0,0,0,0,0" then
"Knowledge Level 4"

Then you can use this formula as your column field in a crosstab. For the summary, use a distinctcount of the agentname field. Then right click on the summary->format field->common->display string and enter:

if currentfieldvalue = 0 then "" else "Yes"

-LB

 

[PhilMyWallet]

I am not sure whether this would work as the table name is table.SkillName. This is a single field with many names each name has a skill assigned.

Does this make sense.

Fantastic formula though!!!!!!



Phil

 

[lbass]

When you showed the spreadsheet with columns like Sales1, Sales2, etc., wasn't this the raw data? Because the spreadsheet shows these are separate fields.

I'm also confused by your reference to the table name as "table.SkillName". The convention is that the table name comes before the period, and the field name comes after it.

-LB

 

[PhilMyWallet]

The table name is AgentSkillsets

The column names are as follows:

Agent Name Skillset Name Skillset Level

The data I supplied was taken from an existing Crosstab I created.


Thanks

 

[lbass]

Wow, you really led me astray! Please answer my question, which I've asked more than once already:

Are the particular level values always the same per "knowledge level"?

-LB

 

[PhilMyWallet]

In answer to you question the Skills will always be the same level if the Knowledge level is the same.

Sorry about the confusion.


Phil

 

[lbass]

If the levels per skill are always the same for a particular knowledge level, then you can use the sum of the levels to distinguish the groups. Create a SQL expression {%sumlevels}:

(select sum(A.`Skillset Level`) from AgentSkillsets A where
A.`Agent Name` = AgentSkillsets.`Agent Name`)

Then create a formula {@KnowLevels}:

if {%sumlevels} = 286 then "Knowledge Level 1" else
if {%sumlevels} = 308 then "Knowledge Level 2" else
if {%sumlevels} = 248 then "Knowledge Level 3" else
if {%sumlevels} = 100 then "Knowledge Level 4"

Then use {@KnowLevels} as your topmost row field in the crosstab.

-LB

 

[PhilMyWallet]

Ok, I see where you are going, but what would happen if the skillset totals are the same across 2 knowledge levels. See example below:

Knowledge 1 = SAles1 @ Level 1 / Sales2 @ Level 2

Knowledge 2 = Sales1 @ Level 2 / Sales1 @ Level 1

They both have the same total but the knowledge levels are different.

It's bit long winded but I could create a nested if statement on the Sum.

Is there an easier solution to this?

Any thoughts?

Phil

 

[lbass]

If that is the case then you still have not answered my question. Please answer this question specifically:

Is a specific knowledge level always characterized by the same values in each skill, e.g., does knowledge level 1 always have the same array of values, i.e.:

30,40,48,0,10,10,48,0,30,30,40

What do you mean by showing a formula for the knowledge levels? What do you mean by referring to sales1 @ level1 or sales2 @ level1.

You are not being long-winded. On the contrary, you are being "short-winded"--not providing enough information. Please show raw data at the detail level based on fields. And please explain your definition of what constitutes a knowledge level. I keep guessing here.

-LB