p &L

[upaliwije]

I have following data in an Excel sheet

Description	Dept	Class	Month	Actual	Budget
Sales	A	1	1	1,000	1,500
Sales	B	2	2	1,000	1,500
Sales	C	1	3	2,000	3,000
Sales	D	1	4	3,000	4,500
Sales	E	3	5	4,000	6,000
Sales	F	4	6	5,000	7,500
Sales	A	1	3	1,000	1,500
Sales	B	2	4	1,000	1,500
Sales	C	1	4	2,000	3,000
Sales	E	1	3	4,000	6,000
Sales	F	1	3	5,000	7,500
PURCHASE	A	1	1	600	900
PURCHASE	B	2	2	4,000	6,000
PURCHASE	C	1	3	1,000	1,500
PURCHASE	D	1	4	1,500	2,250
PURCHASE	E	3	5	2,400	3,600
PURCHASE	F	4	6	3,100	4,650
PURCHASE	A	1	3	800	1,200
PURCHASE	B	2	4	750	1,125
PURCHASE	C	1	4	3,000	4,500
PURCHASE	E	1	3	2,000	3,000
PURCHASE	F	1	3	1,000	1,500
Staff Exp	A	1	1	600	900
Staff Exp	B	2	2	1,000	1,500
Staff Exp	C	1	3	100	150
Staff Exp	D	1	4	400	600
Staff Exp	E	3	5	300	450
Staff Exp	F	4	6	100	150
Staff Exp	A	1	3	800	1,200
Staff Exp	B	2	4	750	1,125
Staff Exp	C	1	4	250	375
Staff Exp	E	1	3	100	150
Staff Exp	F	1	3	500	750
Other Exp	A	1	1	250	375
Other Exp	B	2	2	300	450
Other Exp	C	1	3	300	450
Other Exp	D	1	4	300	450
Other Exp	E	3	5	300	450
Other Exp	F	4	6	300	450
Other Exp	A	1	3	300	450
Other Exp	B	2	4	300	450
Other Exp	C	1	4	300	450
Other Exp	E	1	3	400	-
Other Exp	F	1	3	350	525

Based on the above data I want to design the following output report in a QV document. Pls help me with a sample data model and report

Thanks in advance

P & L Account
Description	Actual	Budget	Varience
Sales	29,000	43,500	(14,500)
Less Purchase	20,150	30,225	(10,075)
Gross Profit	8,850	13,275	(4,425)
Staff Exp	4,900	8,175	(3,275)
Other Exp	3,400	4,500	(1,100)
Total Exp	8,300	12,675	(4,375)
Profit	550	600	(50)

[jerem1234]

Load the data into Qlikview, then use a straight table. The dimension would be Description and it will have 3 expressions:

sum(Actual)

sum(Budget)

sum(Budget)-sum(Actual)

Hope this helps!

[upaliwije]

Thanks

It is not working. I m unable to calculate the Gross Profit, Total Expenses and  Profit

[steve_crosby]

Check out the following from the Resource Library

How to Create a Profit and Loss Statement in QlikView

[PrashantSangle]

Hi,

Use Description as dimension

Write Expression for Gross Profit , Total Expenses and Proft.

1) Gross Profit

sum({<Description={'Sales'}>}Actual)-sum({<Description={'Purchase'}>}Actual)

2)Total Expenses:

sum({<Description={'Staff Exp'}>}Actual)-sum({<Description={'Other Exp'}>}Actual)

and 3)Profit

(sum({<Description={'Sales'}>}Actual)-sum({<Description={'Purchase'}>}Actual))

-

(sum({<Description={'Staff Exp'}>}Actual)-sum({<Description={'Other Exp'}>}Actual))

It is better for you if you calculated all fields at script level instead of trying in Chart

Regards