Pivot Tables and adding in Blank Rows

[james]

All, I know you can add in "Blank Expressions" to simulate blank rows, however, I need to use Dimensions for the rows and not expressions and the Field is called "group"

Does anyone know how to add in a fake or blank segment so that e.g. revenues can have separations to make a cleaner look?

I am using a pivot table and would love the blank lines so that the pivot table would be cleaner from a end user perspective

[#2]

i am also searching for the same argument, but the only solution is (probably) to use straight table

[marcus_sommer]

The easiest way is to add a further dimension as a clone from group then you have a further total-row which you could use with if(dimensionality() = 1, ....

Further you could add a blank-value to group within your datamodel:

blank:

Load * Inline [

group

""

];

Or you could create a dynamically dimension: http://community.qlik.com/blogs/qlikviewdesignblog/2014/04/08/calculated-dimensions

- Marcus

[Mr_H_Huang]

Dimensionality() works for me in Pivot Table. 

In the Load Script, I created an additional field next to the field I need to create blank lines:

LOAD
fields...,
position as WO_POSITION,
'BLANK' & position AS WO_POSITION_BLANK_LINES
RESIDENT TABLE;

In the Pivot Table,

Chart Properties - Presentation, the newly created dummy field, enable Sho Partial Sums.

Dimension:

Background Colour: 

=IF( Dimensionality() <> 5,
RGB(209, 217, 224)
,RGB(255, 255, 255)
)

Expression/Measure:

Expression:

IF( Dimensionality() <> 5,
Sum(TRAN_QTY * TRAN_PRODUCT_WEIGHT)
,' '
)

Background colour:

IF( Dimensionality() = 5,
RGB(255, 255, 255)
)

Text Colour:

=if( Left( [WO_POSITION_BLANK_LINES],5 ) = 'BLANK', RGB(245, 245, 245))

Where the Dimensionality() = 5 is the Blank Lines I needed to add, as attached screenshot. That's who we work around the syntax Dimensionality() in measure, colour expressions. 

[stevedark]

Hi @james 

Similar to @marcus_sommer I would create a dimension table specifically for the pivot that includes the blanks.

Something like:

PivotDims:
LOAD
   RowNum() as PivotOrder,
   PivotDim,
   Group
INLINE [
PivotDim,Group
X,X
Y,Y
Z,Z
,Spacer
];

This way you can use PivotDim as the dimension in the pivot, and sort it by PivotOrder, and it will then associate with the Group and put these in the right place.

The join doesn't need to be on the values in the Group column, you could join on another field.

This method can be particularly useful when doing GL type tables, so you can join to a section of numbers from the PivotDim (so Income, Expenditure, Staff Costs, etc.) and if new GL codes appear they will also join in the correct places.

You can also put style instructions into the PivotDim table, so colour codes and bold etc., so in the formatting of the table you can have this based on that column.

Hope that all makes some kind of sense?

Steve