Group chart by department

[#1]

Hi,

I have a chart that shows sales/person in a team. Every person also belongs to a specific department. I now want the graf to be grouped by department (sorted by name) and then also having the individual sales people deivided into the different departments.

Any ideas? Please see the attached example.

Cheers

[Anonymous]

Two options to consider:

1. Create a drill-down group from fields Department and Sales_Person.  Use this field in your chart instead of Sales_Person field.  Each bar is a Department.  Select one Department, and each bar is a sales person within the selected Department.

2. Use two dimensions in the chart - Department and Sales_Person.  In chart style, select Stacked.  Each bar is a Department consisting of many slices - sales persons.

Regards,

Michael

[marthacano01]

I added 2 options. I hope it helps you.

[#4]

thank you for your advice, however the problem is that i really want to show both department and person at the same time, and in that have it grouped on department. any ideas?

[Anonymous]

Nils, see my option #2 above.

[#6]

sorry micheal, but i dont want it stacked, but rather grouped as i stated above. if you stack a graph you loose the ability to compare the posts properly. i would therefor still like the graph to be sorted on the individual sales people, and then with them grouped on department. as such i would get a graph where the posts indicates the sales/person, where the people then selves then would be grouped on departments. this is why i cannot use a stacked graph.

[Anonymous · Accepted solution]

OK, here is another idea for you to try.
First, make the Department field dual, by loading it this way:

LOAD
dual(Department, RowNo() as Department,
Sales,
Sales_Person,
Sales_Year
FROM (biff, embedded labels, table is Sheet1$)
ORDER BY Department;

Next, in your chart, sort Sales_Person by expression Department, ascending.  I expect that you'll get the grouping by Department after that.
The last part is to make this groups visible.  I think about assigning colors to Departments.  One way to do it is to assign color number in the same load script:

LOAD
dual(Department, RowNo() as Department,
mod(RowNo(),18)+1 as ColNum,       // because there 18 colors in the palette, but you can use less
Sales,
Sales_Person,
Sales_Year
FROM (biff, embedded labels, table is Sheet1$)
ORDER BY Department;

And, use attribute expression for "background color":
=color(ColNum)

Let me know if it works.

Regards,
Michael

[#8]

tnx! that seemed to solve it! (i may still have to work some with the coloring in order for it to look ok, but in principle )