topic Re: P&L account in QlikView

P&L account

upaliwije — Wed, 02 Jul 2014 15:54:42 GMT

I have data in a table as follows

Description	Dept	Class	Month	Amount
Sales	A	1	1	1000
Sales	B	2	2	1000
Sales	C	1	3	2000
Sales	D	1	4	3000
Sales	E	3	5	4000
Sales	F	4	6	5000
Sales	A	1	3	1000
Sales	B	2	4	1000
Sales	C	1	4	2000
Sales	E	1	3	4000
Sales	F	1	3	5000
PURCHASE	A	1	1	600
PURCHASE	B	2	2	4000
PURCHASE	C	1	3	1000
PURCHASE	D	1	4	1500
PURCHASE	E	3	5	2400
PURCHASE	F	4	6	3100
PURCHASE	A	1	3	800
PURCHASE	B	2	4	750
PURCHASE	C	1	4	3000
PURCHASE	E	1	3	2000
PURCHASE	F	1	3	1000
Staff Exp	A	1	1	600
Staff Exp	B	2	2	1000
Staff Exp	C	1	3	100
Staff Exp	D	1	4	400
Staff Exp	E	3	5	300
Staff Exp	F	4	6	100
Staff Exp	A	1	3	800
Staff Exp	B	2	4	750
Staff Exp	C	1	4	250
Staff Exp	E	1	3	100
Staff Exp	F	1	3	500
Other Exp	A	1	1	250
Other Exp	B	2	2	300
Other Exp	C	1	3	300
Other Exp	D	1	4	300
Other Exp	E	3	5	300
Other Exp	F	4	6	300
Other Exp	A	1	3	300
Other Exp	B	2	4	300
Other Exp	C	1	4	300
Other Exp	E	1	3	400
Other Exp	F	1	3	350

I want to prepare a P& L account in the following format using above data. Pls help me to prepare a data model and QV Documnt

P & L Account
Description	Amount
Sales	29000
Less Purchase	20150
Staff Exp	4900
Other Exp	3400
Total Exp	8300
Profit	550

Re: P&L account

julian_rodriguez — Wed, 02 Jul 2014 16:38:50 GMT

Hello Upali,

May be your problem is to sort on a "non alphabetically" way?

Please see the Sort tab, on the example attached...

Regards

Re: P&L account

its_anandrjs — Wed, 02 Jul 2014 16:42:31 GMT

Try to load your table like

LOAD Description,

Dept,

Class,

Month,

Amount

FROM

[RawData.xlsx]

(ooxml, embedded labels, table is Sheet1);

And then take the Pivot table and add the expressions

Label Sales -> sum({<Description={'Sales'}>} Amount)

Label Less Purchase -> sum({<Description={'PURCHASE'}>} Amount)

Label Staff Exp -> sum({<Description={'Staff Exp'}>} Amount)

Label Other Exp -> sum({<Description={'Other Exp'}>} Amount)

Label Total Exp -> sum({<Description={'Staff Exp'}>} Amount) + sum({<Description={'Other Exp'}>} Amount)

Label Profit -> sum({<Description={'Sales'}>} Amount) -

sum({<Description={'PURCHASE'}>} Amount) -

(sum({<Description={'Staff Exp'}>} Amount) +

sum({<Description={'Other Exp'}>} Amount))

And you get table like:-

Note:- I use your data as excel file you use your format.

Regards

Re: P&L account

upaliwije — Thu, 03 Jul 2014 04:30:25 GMT

Thanks Andan

Can U pls upload the QVW document you prepared

Re: P&L account

engr_farhanqadr — Thu, 03 Jul 2014 06:09:12 GMT

Hi Upali,

I agree with the steps of Anand, but there is one more step which is involved for getting your desired shape of table , once you done with the adding of expression you will get all column shown in horizontal way, you have to drag the column to below the first column. attached is the developed file.

Thanks.

Farhan