topic Creation of Calculated Dimension in App Development

Creation of Calculated Dimension

Upali_Wijearatne — Wed, 13 Dec 2023 11:25:31 GMT

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

Re: Creation of Calculated Dimension

Aurelien_Martinez — Wed, 13 Dec 2023 12:14:12 GMT

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

Re: Creation of Calculated Dimension

vincent_ardiet_ — Wed, 13 Dec 2023 13:30:24 GMT

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.

Re: Creation of Calculated Dimension

Upali_Wijearatne — Wed, 13 Dec 2023 14:00:13 GMT

Thanks for your reply. But I can not understand this since I am new to qlikview Please. Pls simplify it