topic Re: Pivot Tables and adding in Blank Rows in QlikView

Pivot Tables and adding in Blank Rows

james — Mon, 13 Jul 2009 22:01:19 GMT

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

Re: Pivot Tables and adding in Blank Rows

Thu, 11 Sep 2014 14:38:46 GMT

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

Re: Pivot Tables and adding in Blank Rows

marcus_sommer — Thu, 11 Sep 2014 16:24:34 GMT

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

Re: Pivot Tables and adding in Blank Rows

Mr_H_Huang — Fri, 21 Nov 2025 11:28:31 GMT

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 tho 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 shown in the attached screenshot. That's how I worked around the syntax Dimensionality() in measure, colour expressions.

Re: Pivot Tables and adding in Blank Rows

stevedark — Fri, 31 Oct 2025 09:48:34 GMT

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