Using Expand/Collapse in a Pivot Table containing Calculated Dimensions may show fewer values than expected.
This may occur if the Calculated Dimension is based upon two or more fields.
Values missing after expanding (when Calculated Dimension is used)
- Open attached example "CalculatedDimmension.qvw".
- Investigate how the Calculated Dimension is defined
- Collapse/expand the Pivot Table. Note the difference
Add calculated field within the script
- In the script, add the same value as “testData” field in table DATA
- Add testData as dimension
- Collapse/expand the Pivot Table. Note that this works as expected.
This is due to the Calculated Dimension containing two field references (in the above example, “DimYear” and “DimMonth”), each with their own dimensionality. Calculated Dimensions that contain more than one field reference result in a very different behavior with selections, in addition to how dimensions are expanded and/or related to the expression(s) in the object.
When defined in the script, this results in a single field reference and the result is a single, well defined field that is more similar to a calculated dimension containing a single field reference.
It is preferable to define a Calculated Field within the script if the Calculated Dimension is based upon two or more fields.