Producing a formatted report - P&L / Balance Sheet

[#1]

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:-

		October	September
Current Assets
	Cash
	Cash At Bank and on Hand	9,561,008	7,738,286
	Deposits on Call	-	-
	Receivables
	Trade Debtors	5,725,400	6,209,081
	Other Debtors	1,232,776	1,147,345
	Inventories	-	-
	Other Current Assets
	Prepayments	92,746	164,699
Total Current Assets		16,611,931	15,259,411
Non-Current Assets
	Receivables
	Investments
	Shares in Listed Companies	730,000	730,000
	Other Investments	184,141,870	182,406,279
	Investments in JV	(28,259,420)	(28,259,420)
	Property, Plant & Equipment
	Land, Buildings, Plant & Equipment	1,111,539	1,099,679
	Mine Properties	122,431	129,848
	Exploration and Evaluation Expenditure
	Additional Exploration and Evaluation	353,201	353,201
	Intangibles	-	-
	Other Non-Current Assets
	Taxation	45,000	45,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:

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.

[disqr_rm]

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...

[#3]

Thanks Rakesh, that's perfect.

[anitamelbye]

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

[#5]

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

[#6]

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

Line Type	Line Text	Start	End
H	Current Assets	1000	1400
B		1000	1400
N	Cash	1000	1050
N	Prepayments	1050	1100

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).

[#7]

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

[#8]

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

[#9]

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!

[#10]

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.