Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Producing a formatted report - P&L / Balance Sheet

Hi Guys,

I need to produce a formatted report which displays like a classic report, with group headers, group footers going down the page, rather than across the page.

I will show you what I need to produce and then a couple of examples of what I have produced.

Below is what i need to produce:-

OctoberSeptember
Current Assets
Cash
Cash At Bank and on Hand9,561,0087,738,286
Deposits on Call--
Receivables
Trade Debtors5,725,4006,209,081
Other Debtors1,232,7761,147,345
Inventories--
Other Current Assets
Prepayments92,746164,699
Total Current Assets16,611,93115,259,411
Non-Current Assets
Receivables
Investments
Shares in Listed Companies730,000730,000
Other Investments184,141,870182,406,279
Investments in JV(28,259,420)(28,259,420)
Property, Plant & Equipment
Land, Buildings, Plant & Equipment1,111,5391,099,679
Mine Properties122,431129,848
Exploration and Evaluation Expenditure
Additional Exploration and Evaluation353,201353,201
Intangibles--
Other Non-Current Assets
Taxation45,00045,000
Intercompany Loans(352,327,861)(346,755,885)
Total Non-Current Assets(194,083,241)(190,251,300)
TOTAL ASSETS(177,471,310)(174,991,889)

Next, is what I have produced:

error loading image

error loading image

The problem with the pivot table is that the pivot (as per any 'pivot') works from left to right, across the page. You can't apply a unique identifer to the 'Total' row to identify which Total the Total refers to.

The problem with the straight table is that the group names are repeated and there is no facility to create sub-totals without scripting this in.

I've tried using 'Indent Mode' but the only way you can get it to look decent is using 'Totals on First Row' which is unacceptable.

Any suggestions are very welcome!

Thanks.

1 Solution

Accepted Solutions
disqr_rm
Partner - Specialist III
Partner - Specialist III

Here is the zip, again.

View solution in original post

13 Replies
disqr_rm
Partner - Specialist III
Partner - Specialist III

You would need to produce mapping table in which you would need to add all the lines above and show as total line. Pretty cool trick in QV. Here is an example: http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.PostAttachments/00.00.07.16...

Not applicable
Author

Thanks Rakesh, that's perfect.

anitamelbye
Creator
Creator

This actually solved my problem (challenge) as well!! THANK YOU!

Not applicable
Author

I'm creating a balance sheet and have already got most of the way to creating something I like (along the lines of your one). i had two questions, if you have a sec to answer them...

How did you get the blank lines on the report please? also for the headers at the top of each section e.g. current assets - ho did you manage to get those?

Many thanks

Not applicable
Author

If you are creating your report using an interval match, in your spreadsheet, specify header lines and blank lines with ranges. i.e.

Line TypeLine TextStartEnd
HCurrent Assets10001400
B10001400
NCash10001050
NPrepayments10501100

Then use your Line Type field to hide/ show figures (expressions). I.e. Text Colour, if LineType = 'B' OR 'H' then RGB(255,255,255) ELSE RGB(0,0,0).

Not applicable
Author

Hi again Matt

thanks for the last reply - all looking good now. Just wondered if you had any tips on the set analysis for the expressions to pull out the balance this month and last?

thanks

Not applicable
Author

Hi everyone,

I guess I'm trying something similar or related from what its in here. However my data model its a little bit different.

I have a big accounting map with different numeric operators for every account like +, -, *, / and even ~ which means do nothing, just show the number without aggregation for statistical measures like Headcount, Units, Average Unit Price etc.

I also need to make calulations just between accounts of the same accounting type, for example Assets and Liabilities separated from Revenue and Expenditures.

Example:

AccountCodeAlternateKey

ParentAccountCodeAlternateKey

AccountDescription

1

NULL

Balance Sheet

10

1

Assets

110

10

Current Assets

1110

110

Cash

1120

110

Receivables

1130

1120

Trade Receivables

1140

1120

Other Receivables

1150

110

Allowance for Bad Debt

1160

110

Inventory

1162

1160

Raw Materials

1164

1160

Work in Process

1166

1160

Finished Goods

Assets

+

1170

110

Deferred Taxes

Assets

+

1180

110

Prepaid Expenses

Assets

+

1185

110

Intercompany Receivables

Assets

+

1200

10

Property, Plant, Equipment

Assets

+

1210

1200

Land & Improvements

Assets

+

1220

1200

Buildings & Improvements

Assets

+

1230

1200

Machinery & Equipment

Assets

+

1240

1200

Office Furniture & Equipment

Assets

+

1250

1200

Leasehold Improvements

Assets

+

1260

1200

Construction In Progress

Assets

+

1300

10

Other Assets

Assets

+

20

1

Liabilities and Owners Equity

Liabilities

-

210

20

Liabilities

Liabilities

+

2200

210

Current Liabilities

Liabilities

+

2210

2200

Notes Payable

Liabilities

+

2230

2200

Accounts Payable

Liabilities

+

2300

2200

Accrued Expenses

Liabilities

+

2310

2300

Salary & Other Comp

Liabilities

+

2320

2300

Insurance

Liabilities

+

In Microsoft Analysis Services this is something pretty easy to do but in QlikView I couldn't find a way yet. This example is from the Adventure Works database and my customers database modelling is exactly the same.

Could anyone please help me on this? Its very important to me.

thanks in advance,

Hernán

Not applicable
Author

Mr. Matt,

Sir can you attach your sample data here just for learning purposes cause i am new to qlikview..

Great job!

thank you very much!

Not applicable
Author

Hi guys, Sorry for no replies to this thread. I don't have a sample app, but if you can tell me where you are having problems i will help you out.