Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Hello Upali,
May be your problem is to sort on a "non alphabetically" way?
Please see the Sort tab, on the example attached...
Regards
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
Thanks Andan
Can U pls upload the QVW document you prepared
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