Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friend
I have in my data model a Pivot Table 1 as shown below and I Want my pivot table to be modified(Pivot Table2) to enable me calculate the net profit . I tried to do but not getting the desired result. Kindly Help to modify the my pivot table 1 so that I can have Pivot Table 2
Hi,
If you can add data to your model, you can create a PL_Group like :
PL_Description the link to your data
PL_Group the dimension you can use
Sign if is minus (like costs)
Order for good sorting
PL_Description | PL_Group | Sign | Order |
Revenue | Revenue | 1 | 1 |
COGS | COGS | 1 | 2 |
Revenue | Gross Profit | 1 | 3 |
COGS | Gross Profit | -1 | 3 |
Staff Expenses | Staff Expenses | 1 | 4 |
Administration Expenses | Administration Expenses | 1 | 5 |
Selling & Didtribution Ex | Selling & Didtribution Ex | 1 | 6 |
Staff Expenses | Total Expenses | 1 | 7 |
Administration Expenses | Total Expenses | 1 | 7 |
Selling & Didtribution Ex | Total Expenses | 1 | 7 |
Other Income | Other Income | 1 | 8 |
Revenue | Net Profit | 1 | 9 |
COGS | Net Profit | -1 | 9 |
Staff Expenses | Net Profit | -1 | 9 |
Administration Expenses | Net Profit | -1 | 9 |
Selling & Didtribution Ex | Net Profit | -1 | 9 |
Other Income | Net Profit | 1 | 9 |
You can use a expression like :
Sum(value*Sign)
The resut :
Bonus : you can add a column with color code
You can maybe use 2 dimensions and creates a table to classify your PL_Description, something like:
PL_Descriptions:
Load * Inline
[ PL_Description, PL_Group, DebitCredit
Revenue,Gross Profit,1
COGS,Gross Profit,-1
Staff Expenses,Total Expenses,1
Administration Expenses,Total Expenses,1
Selling & Distribution Expenses,Total Expenses,1
Other Income,Other Income,1];
Then, you create a pivot table with PL_Group, PL_Description and Sum(Actual)*DebitCredit as expression.
Thanks for your reply. But I can not understand this since I am new to qlikview Please. Pls simplify it