Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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
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
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.
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.
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.
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