Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Insert Blank Lines in Pivot Table

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

Pivot.bmp

15 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Insert Blank Lines in Pivot Table

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.

Highlighted
Not applicable

Insert Blank Lines in Pivot Table

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

Highlighted
MVP & Luminary
MVP & Luminary

Insert Blank Lines in Pivot Table

Hi,

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

Regards,

Jagan.

Highlighted
Not applicable

Re: Insert Blank Lines in Pivot Table

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

Highlighted
Not applicable

Re: Insert Blank Lines in Pivot Table

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

Highlighted
Luminary
Luminary

Re: Insert Blank Lines in Pivot Table

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.

P&L.PNG

Hope it's of use and good luck.

All the best,

Matt - Visual Analytics Ltd

Highlighted
Not applicable

Insert Blank Lines in Pivot Table

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

Highlighted
Not applicable

Re: Insert Blank Lines in Pivot Table

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!

Highlighted
Not applicable

Re: Insert Blank Lines in Pivot Table

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.