Selecting missing values of a dimension in a pivot table

[#1]

Hello

I have a dimension (from file A) in a pivot table that is showing a missing value "-". This is because there are some figures in the body of the table (from file B) that have not linked to any value of the dimension (in file A). the problem I'm having is that I cannot select this missing level of the dimension, QV won't let me. Is there any way to select ths missing value?

I've attached an example to make this clearer. In the table you can select males, or females, but not the "-" group.

Many thanks.

[khadeer]

PFA,

I think u want this only, Let me know if any issues

[#3]

Hello

No, I don't want to surpress the missing values. I need to be able to see them. But I can't select them and that's the problem.

Thanks.

[flipside]

Hi James,

You can either add 's*x' as a second dimension which can be selected OR create a calculated dimension such as ...

=if(IsNull(description),s*x,description)

... but this changes the way the object deselects (you have to either clear or use another object to deselect - in QV11 at least).

flipside

PS Had to mask the field name otherwise would be moderated!!

[#5]

I've added a calculated dimension to replace the null with '?' but I still can't select it when I click on it

[flipside]

It has to be an alternative existing field rather than a hard-coded value.

flipside

[jonathandienst]

Hi

I dont think you can easily fix this from the front end - the data model has nothing to select. You should be able to construct a straight table that shows the fact records missing that attribute, but you still wont be able to perform a selection based on the missing attribute. You would be able to drag select the record identifier field (assuming that you included it in your object.

Looking at your example, there is a mismatch between the possible values for [sex] in the fact table (fileA) and the values in the dimension table (fileB). Have a look at the update to your model (attached). This update fixes the mismatch by adding the missing values to the dimension table with the description 'Unknown'.

Hope that helps

Jonathan