P&L account

[upaliwije]

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

[julian_rodriguez]

Hello Upali,

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

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

Regards

[its_anandrjs]

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

[upaliwije]

Thanks Andan

Can U pls upload the QVW document you prepared

[engr_farhanqadr]

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