topic Producing a formatted report - P&L / Balance Sheet in QlikView

Producing a formatted report - P&L / Balance Sheet

Wed, 10 Feb 2010 03:57:58 GMT

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.

Producing a formatted report - P&L / Balance Sheet

disqr_rm — Fri, 12 Feb 2010 04:36:50 GMT

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.08/StatementOfAccount.zip

Producing a formatted report - P&L / Balance Sheet

Mon, 15 Feb 2010 04:05:47 GMT

Thanks Rakesh, that's perfect.

Producing a formatted report - P&L / Balance Sheet

anitamelbye — Tue, 16 Feb 2010 20:26:44 GMT

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

Producing a formatted report - P&L / Balance Sheet

Mon, 07 Jun 2010 16:23:55 GMT

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

Producing a formatted report - P&L / Balance Sheet

Tue, 08 Jun 2010 02:09:06 GMT

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

Producing a formatted report - P&L / Balance Sheet

Wed, 16 Jun 2010 00:02:18 GMT

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

Producing a formatted report - P&L / Balance Sheet

Thu, 04 Nov 2010 11:54:22 GMT

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

Producing a formatted report - P&L / Balance Sheet

Tue, 09 Nov 2010 09:28:18 GMT

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!

Producing a formatted report - P&L / Balance Sheet

Tue, 09 Nov 2010 10:27:14 GMT

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.

Producing a formatted report - P&L / Balance Sheet

Fri, 11 Feb 2011 16:03:04 GMT

Hi Rakesh,

I came across your post, and experimented with your suggestion. It really works -thanks!!

One question: getting totals, subtotals, etc. is now easy for me. However, do you have any suggestions for putting in lines for various ratios e.g. Gross Profit% i.e (Turnover -Cost Of Sales)/ Turover. Your input will be really appreciated!

Cheers

Arie

Producing a formatted report - P&L / Balance Sheet

denwo2010 — Mon, 09 May 2011 21:33:13 GMT

has anyone got a example of this? as the link no longer works

Re: Producing a formatted report - P&L / Balance Sheet

disqr_rm — Mon, 09 May 2011 23:02:13 GMT

Here is the zip, again.

Re: Producing a formatted report - P&L / Balance Sheet

kalyandg — Mon, 15 Oct 2012 07:27:36 GMT

hi rajesh,

i cant get the zip file thru that link b'coz the page is not found, so please attach the zip file once again