Insert Blank Lines in Pivot Table

[#1]

Does anybody know a way I can insert blank lines into a pivot table or change the size of the font based on an expression

As you can see from the image below, I have been able to change to background colour and font style based on the dimension value by using expressions like =if(Total_Column> 0,'<B>' ,) , but my sponsor is being quite particular about wanting blank lines inserted after 'Net Property Income', Total amin Exes'  and ' Operating result'

He also wants to increase the font size of these lines. Can this be done?

Many thanks,

Lawrence

[jagan]

Hi,

Just add an empty expression in the Pivot table

Expression Name = ' ' (Single Space)

Expression          = ''

and adjust the coulmn width as you wish for this empty column

Hope this helps you.

Regards,

Jagan.

[#3]

Thanks Jagen,

I have already added Blank expressions in the screen shoot above (column 5 and Column 10)

however i need to insert a blank row in specific places ie in the dimension. If a add balnk values in the dimension it places thes at the bottom of the table and will not take notice of the sort order because the values are null. If I artifically insert a dummy value, with mucks up my partial sums.

Lawrence

[jagan]

Hi,

In the first column those all are expressions or dimension values?

Regards,

Jagan.

[#5]

The first column is a dimension called .

On the data load I also create an associated data table to determin the format of the diminsion and expressions.

load * Inline

[COSTGROUP, ORDER, Total_Column, Calc_Type

Rental Income, 1,0,Sum

Property Outgoings ,2,0,Sum

Net Property Income , 3,1,Sum

Personnel Costs, 5,0,Sum

Other Administrative Expenses, 6,0,Sum

Total Administrative Expenses, 7,1,Sum

Fee Income, 9,0,Sum

Net Operating Costs, 10,1,Sum

Operating Result , 12,2,Sum

Net operating costs %, 14,3,Aggr

Net Interest Payable, 16, 1,Sum

Underlying Profit,18, 2, Sum

];

and tried inserting blank line in this table, but it resulted in all values going to the bottom.

load * Inline

[COSTGROUP, ORDER, Total_Column, Calc_Type

Rental Income, 1,0,Sum

Property Outgoings ,2,0,Sum

Net Property Income , 3,1,Sum

Blank1,4 ,1, Blank

Personnel Costs, 5,0,Sum

Other Administrative Expenses, 6,0,Sum

Total Administrative Expenses, 7,1,Sum

Blank2,8 ,1, Blank

Fee Income, 9,0,Sum

Net Operating Costs, 10,1,Sum

Blank3,11 ,1, Blank

Operating Result , 12,2,Sum

Blank4,13 ,1, Blank

Net operating costs %, 14,1,Aggr

Blank5,15 ,1, Blank

Net Interest Payable, 16, 1,Sum

Blank6,17 ,1, Blank

Underlying Profit,18, 2, Sum

];

many Thanks ,

Lawrence

[#6]

Hi Lawrence,

Unfortunatly I cannot help you with oyur blank line problem as I'm struggling with this as well. I am however very interested in the way you formatted single rows. I tried to reproduce this based on what you wrote down but to no avail. Do you have some more explanation on how you did this?

Cheers a lot!

Erik

[matt_crowther]

The wonderful world of getting Financial Reporting to format correctly in Qlikview!

As a consultant this was the bain of my life, the nearest / best solution I found was to bin the Pivot Table altogether as it's not flexible enough and use a Straight Table instead controlled by an Excel based 'Format Sheet'.

I came up with my own method but the similar one attached is neatly packaged and was created by Stefan Walther.

Hope it's of use and good luck.

All the best,

Matt - Visual Analytics Ltd

[#8]

The way I format a single line is based on a simple If statement. in both the dimension and expression, eg if(Column_type=2, RBG(219,229,241), if(Column_type = 3, RGB(182,221,232), White())).

colum_type refrers to a formating table created during the dataload process.

[COSTGROUP, ORDER, Column_type, Calc_Type
Rental Income, 1,0,Sum
Property Outgoings ,2,0,Sum
Net Property Income , 3,1,Sum
Personnel Costs, 5,0,Sum
Other Administrative Expenses, 6,0,Sum
Total Administrative Expenses, 7,1,Sum
Fee Income, 9,0,Sum
Net Operating Costs, 10,1,Sum
Operating Result , 12,2,Sum
Net operating costs %, 14,3,Aggr
Net Interest Payable, 16, 1,Sum
Underlying Profit,18, 2, Sum
]

Hope this helps,

;

Lawrence

[#9]

Hi Mathew,

This is a great example.

I was wondering if you could share with me how did you apply bold font to totals? I am loading P&L data directly from excel file. I am not applying any calculations, is this even possible in QV?

Thank you!

[#10]

Dear  zvirina123,

to make the font style bold  for The Total

on each expression  + sign

goto textformat add the expression...... Format

FInd the attachement,

It will help you.

Thanks,

Mukram.