topic Re: possible in which way? in QlikView

possible in which way?

arulsettu — Tue, 05 May 2015 04:10:01 GMT

Hi,

i am trying to do something like in following excel. so i followed everything according to this concept

How to Create a Profit and Loss Statement in QlikView

but i cant able to achieve what i need.

can anyone tell me how to achieve this

Thanks

Re: possible in which way?

ToniKautto — Tue, 05 May 2015 04:44:00 GMT

What are you saying, the How to Create a Profit and Loss Statement in QlikView is not working as described? If yes, then I think you should address that by asking followup questions in the How to Create a Profit and Loss Statement in QlikView thread.

The attached Excel file only contains headers. I am quite sure you can accomplish headers like this with the guidance from How to Create a Profit and Loss Statement in QlikView‌. If this is not true, then I think the difference might be the data you are loading into QlikView. Could you provide a sample QVW and source data file so that someone can take a closer look at the actual problem your are having?

Re: possible in which way?

arulsettu — Tue, 05 May 2015 05:22:37 GMT

if i change the parameter it showing nothing.

anyone suggest some ideas

Thanks

Re: possible in which way?

arulsettu — Tue, 05 May 2015 07:14:11 GMT

any ideas guys?

Re: possible in which way?

Peter_Cammaert — Tue, 05 May 2015 07:44:56 GMT

What does the field ACNT_COMP_CODE contain? It has 4 values which at the time of the INTERVALMATCH fall in every Start/End range. That cannot be the purpose...

Somehow you should divide your P&L layout into two sections:

LowLevel sections that group accounts into ranges
HighLevel sections that aggregate one or more LowLevel sections and / or HighLevel sections.

Peter

Re: possible in which way?

Peter_Cammaert — Tue, 05 May 2015 08:04:28 GMT

There you have it! Why are you using INTERVALMATCH to map a company code into a P&L range? A P&L range specifies which account groups should be aggregated in this particular line. Not company codes...

For a suggestion on how to do this, see my previous post.

Peter

Re: possible in which way?

arulsettu — Tue, 05 May 2015 08:09:17 GMT

can you give a example based on my excel please i cant understand.

Thanks

Re: possible in which way?

Peter_Cammaert — Tue, 05 May 2015 08:25:46 GMT

No because the problem cannot be solved using the excel. The excel is ok. Your other sources are lacking some essential data (and the source QVDs are missing altogether)

Reread the Technical Brief in How to Create a Profit and Loss Statement in QlikView. On page 6 there is a field called [Reporting Code] which maps every account in the account table to a P&L item. This Reporting code is linked to the various P&L lines where these accounts should be aggregated. Make sure that every account has a place in the P&L, assign the corresponding Reporting Code to the account and replace ACNT_COMP_CODE with [Reporting Code] in the INTERVALMATCH. Reload. Job done.

Peter

Re: possible in which way?

arulsettu — Tue, 05 May 2015 08:53:00 GMT

Hi,

How they are generating reporting code in P&L sample app further how can i generate reporting code based on my data which will be associated to ACNT_COMP_CODE and i'm having a field Acent_Felx_02 which is acting like flag having data as Balance and Income statement.

In the sample app they are having field Account type having data as Balance and P&L.

Please suggest me how can i proceed?

Re: possible in which way?

Peter_Cammaert — Tue, 05 May 2015 08:57:09 GMT

For answers to both questions, contact your accounting dept. They decide which General Ledger account goes into which P&L / Balance reporting category.

Peter

Re: possible in which way?

arulsettu — Tue, 05 May 2015 09:05:44 GMT

like this i have to create reporting_code in DB

ACNT_FLEX_04 reporting_code

1- Gross premiums	1
2- Reinsures share of gross premiums	2
XOL Protection costs	3

3- Movement in unexpired premium	4

4- Net Commision	5
5- Gross claims	6
6- Reinsurance and other recoveries	7
8- Movement in outstanding claims	8

Operative and administrative expenses	9
Provision for doubtful debts	10
Depreciation of property and equipment	11
Group shared expenses	12
Investment income	13
Intercompany Facility interest	14
Intercompany Dividend	15
Rental income	16
Advisory fee income	17
Other Income	18

Thanks

Re: possible in which way?

Peter_Cammaert — Tue, 05 May 2015 09:56:49 GMT

Almost. If you want to know the logic behind what I'm going to explain, reread the technical brief. It would be even more instructive if you rebuild step-by-step the P&L as documented by Jennell, based on her data instead of jumping straight into your own challenges.

The Reporting Codes that you should assign to the accounts in your chart of accounts are taken from your Excel (see income_statement_new.xls). The Excel has two columns ExecPLStart and ExecPLEnd. All lines that have identical values in both columns are carrying Reporting Codes. This is almost identical to the list you supplied above, except that the last one, BOD Remuneration & expenses (19) is missing.

Now go back to your list of accounts and assign a single Reporting Code to each account number. This is where you can do with some information from your accounting dept.

Peter

Re: possible in which way?

arulsettu — Tue, 05 May 2015 10:04:08 GMT

Thank you very much i will check and comeback

Re: possible in which way?

arulsettu — Thu, 07 May 2015 04:49:47 GMT

Thanks Peter it works...

Re: possible in which way?

arulsettu — Thu, 07 May 2015 05:08:00 GMT

Hi Peter,

just one doubt space between rows is not coming. i checked the excel the 's' is available.

Thanks

Re: possible in which way?

Peter_Cammaert — Thu, 07 May 2015 07:47:22 GMT

See Technical Brief, p8, first bullet. Did you put spaces in ...?

Re: possible in which way?

arulsettu — Thu, 07 May 2015 08:39:49 GMT

yes i did