Table Chart : List all values related to a dimension

[#1]

Hi all !

Here is my problem : I have projects linked to a department.

Data look like this :

Project	Department
PRJ_1	US
PRJ_2	US
PRJ_3	US
PRJ_4	UK
PRJ_5	UK
PRJ_6	FR
PRJ_7	FR
PRJ_8	FR
PRJ_9	JPN

I would like to buid a table, listing my projects by department. It should look as below :

US	UK	FR	JPN
PRJ_1	PRJ_4	PRJ_6	PRJ_9
PRJ_2	PRJ_5	PRJ_7
PRJ_3		PRJ_8

=> Table box displays distinct line for each project but is not customisable (pivoting line to column is not possible. Merge header cells is not possible too)

=> Straight/Pivot table works with dimension and expression. US, UK and FR departments have several projects and QV can't display them all (it shows "-" as null because QV can't "choose" only one value). It seems like I have to aggregate.

The closest solution I have is to concatenate projects for each department :

=Concat (distinct [Project Code], Chr(10))

I get this result but it's not what I want. User must be able to select (by click) project, one by one and not a list :

US	UK	FR	JPN
PRJ_1 PRJ_2 PRJ_3	PRJ_4 PRJ_5	PRJ_6 PRJ_7 PRJ_8	PRJ_9

Any ideas ?

Thanks in advance

Regards,

Julien

[#2]

Hi,

Take a Pivot Table with two Dimensions and in Expression give it Sum(1) and Drag the Department drop at the Horizantal Position. and then go to genral tab take straight table go to the presentation tab in the same chart hide the Expression and then again go to the General Tab change the chart type from Straight to Pivot.

Thanks & Regards

Santhosh Kumar G

[#3]

Hi Santhosh,

Your trick is interesting, it almost works. After turning my pivot table to straight table, hiding expression column, i get this (It can be done, directly in Straight table, turning "Horizontal" option on in Presentation tab) :

US			UK		FR			JPN
PRJ_1	PRJ_2	PRJ_3	PRJ_4	PRJ_5	PRJ_6	PRJ_7	PRJ_8	PRJ_9

When I switch back to pivot table, hidden colum re-appears...

It's close to what I need but I would like to have projects on different lines (and not columns) because of width space in my dashboard.

Thanks Santhosh

Regards,

Julien

[#4]

Hi Julien,

We don't have a option in Straight Table like this.

the other query is Formatting issue.try to reduce the space between cells

Thanks & Regards,

Santhosh Kumar G

[#5]

Hi Santhosh,

With a lot of projects per department, I can't display it on an horizontal way, even if I reduce space between cells. Is there no solution to have this kind of vertical list ? I don't understand why QV process is not able to calculate this list.

Any ideas ?

Thanks and regards,

Julien

[johnw · Accepted solution]

Have a look at the third chart on the right in the attached file.  It does this:

Dimension = valueloop(1,7) // use as many as you might need
Expression = subfield(concat(PhaseDesc,','),',',valueloop(1,7))

There may be an easier way (I certainly hope there is, since this seems like such a basic requirement), but that's the first one that occurred to me when someone asked a similar question a few days ago.

[tanelry]

Your requirement is mission impossible because there is no relationship between projects.

PRJ_1, PRJ_4, PRJ_6 and PRJ_9 should have a common value on vertical dimension, to be able to display in one row.

So you need to modify your data to relate the projects.

Maybe like this:

ProjectNo     Project     Department

1        PRJ_1        US

2        PRJ_2        US

3        PRJ_3        US

1        PRJ_4        UK

2        PRJ_5        UK

1        PRJ_6        FR

2        PRJ_7        FR

3        PRJ_8        FR

1        PRJ_9        JPN

Then you can create a pivot table with ProjectNo and Department as dimensions and Only(Project) as expression.

[johnw]

One problem with assigning a ProjectNo in the script is that then the number is fixed, so won't be responsive to selections.  That means you could end up with blanks in the list if any of the projects are excluded by selections.  Fortunately, we can assign a ProjectNo on the fly, in the chart itself, by using a valueloop() as demonstrated in my example above.

[#9]

What a tip John !! It's a little bit tricky but it works. I have to play with color and font to hide the valueloop column but it's okay.

Thanks both of you, your help is very appreciated !

Regards,

Julien