QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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:-

 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.

1 Solution

Accepted Solutions
Highlighted
Partner

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

Here is the zip, again.

13 Replies
Highlighted
Partner

Producing a formatted report - P&L / Balance Sheet

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

Highlighted
Not applicable

Producing a formatted report - P&L / Balance Sheet

Thanks Rakesh, that's perfect.

Highlighted
Contributor

Producing a formatted report - P&L / Balance Sheet

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

Highlighted
Not applicable

Producing a formatted report - P&L / Balance Sheet

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

Highlighted
Not applicable

Producing a formatted report - P&L / Balance Sheet

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

Highlighted
Not applicable

Producing a formatted report - P&L / Balance Sheet

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

Producing a formatted report - P&L / Balance Sheet

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.

Hernán

Highlighted
Not applicable

Producing a formatted report - P&L / Balance Sheet

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!

Highlighted
Not applicable

Producing a formatted report - P&L / Balance Sheet

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.