topic Re: Insert Blank Lines in Pivot Table in QlikView

Insert Blank Lines in Pivot Table

Mon, 19 Dec 2011 09:59:34 GMT

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

Re: Insert Blank Lines in Pivot Table

jagan — Mon, 19 Dec 2011 13:40:40 GMT

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.

Insert Blank Lines in Pivot Table

Mon, 19 Dec 2011 13:49:46 GMT

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

Insert Blank Lines in Pivot Table

jagan — Mon, 19 Dec 2011 13:57:06 GMT

Hi,

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

Regards,

Jagan.

Re: Insert Blank Lines in Pivot Table

Mon, 19 Dec 2011 14:04:37 GMT

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

Re: Insert Blank Lines in Pivot Table

Wed, 21 Dec 2011 15:22:43 GMT

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

Re: Insert Blank Lines in Pivot Table

matt_crowther — Wed, 21 Dec 2011 16:29:58 GMT

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

Insert Blank Lines in Pivot Table

Thu, 22 Dec 2011 16:01:48 GMT

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

Re: Insert Blank Lines in Pivot Table

Thu, 17 Jan 2013 19:12:13 GMT

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!

Re: Insert Blank Lines in Pivot Table

Thu, 17 Jan 2013 19:40:49 GMT

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.

Re: Insert Blank Lines in Pivot Table

sebastiandperei — Thu, 17 Jan 2013 20:52:58 GMT

Hi Lawrence!

Just add another column, with the order that you want to show the rows. This will cause you get the blank files placed where you want.

Re: Insert Blank Lines in Pivot Table

Fri, 18 Jan 2013 13:47:34 GMT

Can you please recomend text that i can use to format specific rows on my table? I got the first row to be set to Bold but i can't get the other once working. Here is my expression:

=if(Description='Revenue',('<B>')).

Thank you

Re: Insert Blank Lines in Pivot Table

Fri, 18 Jan 2013 14:10:15 GMT

I also tried this with no luck

if(Consolidated='Revenue'

,'<b>'

if(Consolidated='Cost of Goods Sold'

,'<b>'

,'Null'))

Re: Insert Blank Lines in Pivot Table

Fri, 18 Jan 2013 15:14:29 GMT

This is the P&L Pivot table I currently Use and I use a combination of background formatting and text formatting to get this this.

For the background colours I us a formula like this.

=if(Total_Column = 2,rgb(219,229,241),if(Total_Column=3,RGB(182,221,232),if(Total_Column=4,RGB(214,231,248))))

and text formatting formula of

=if(Total_Column>0,'' ,null())

Don’t forget to apply the formatting to both dimensions and expressions.

Lawrence

Re: Insert Blank Lines in Pivot Table

Thu, 07 Jan 2016 10:45:17 GMT

Hi everyone.

I was struggling with the same problem and found a simple solution.

Start by inserting distinct values beggining with the same substring on the script/file where you want the blank lines: e.g. BlankLine1, BlankLine2, and so on.
On dimensions tab, Used Dimensions, apply format the text color with the code:
1. if( Left( [Dimension name],9 ) = 'BlankLine', RGB(255,255,255))
Next you just have to make sure that all the expressions compare the dimension value with Left( [Dimension name],9)='BlankLine' and assign value null.
You also have to sort the dimension values by "Load Order"

Thank you 😃

Re: Insert Blank Lines in Pivot Table

Tue, 03 Jan 2017 07:43:13 GMT

Hi Lawrence, Can you please post a sample QVW?