Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Pivot table with two dimensions (Proj, Name) and one expression (Hours).
In the case where there is only one name associated with the project, I need to replace the name with the tag "Confidential". Where there are more than one name, all names should be displayed.
I replaced the "Name" dimension with =If(count(Name)=1,'Confidential',Name). While the edit expression claimed it was OK, the table said "// Error in calculated dimension".
See below an example of what I trying to accomplish.
Any thoughts?
It works when I add in the "NODISTINCT" to the Aggr function.
=AGGR(IF(ONLY(AGGR( NODISTINCT COUNT(DISTINCT Name), Project))=1, 'Confidential', Name), Project, Name)
JLR
Although I am not a fan of calculated dimensions you can try:
Aggr(If(count(Name)=1,'Confidential',Name),Proj)
Remember to supress null values.
I would rather do this in the script by changing the key for a project if it only has one name associated with it. Then I would create a single user in the Name Dimension with the value 'Confidential'.
Then you can just use Proj and Name as dimensions and people will not be able to extract the information from the model.
By using a calculated dimension it will be possible to select the project and see the name as the only possible value in a Name listbox.
Simen,
That sort of worked. It showed "Confidential" where I wanted it, --but-- it collapsed (aggregated) all of the multiple names / project into one line with a "-".
I agree with your concerns, but I was hoping to fix this in the table without touching the data structures.
Hi There.
See QVW attached.
Kind Regards,
Ernesto.
Ernesto,
That expression works in the app you provided, but not in my app, where it gives the same results as =Name.
I'm still investigating.
I'm using 11.2 SR4 - what version are you using?
John
Strange I'm using a very old version indeed 11.00 SR 1 in my workplace, gotta try this later at home with 11.20 SR 8
Kind Regards,
Ernesto.
it works with 11.20 SR 5!
It works when I add in the "NODISTINCT" to the Aggr function.
=AGGR(IF(ONLY(AGGR( NODISTINCT COUNT(DISTINCT Name), Project))=1, 'Confidential', Name), Project, Name)
JLR
hmm interesting...thanks!