topic Re: P & L RATIO in QlikView

P & L RATIO

sagaraperera — Thu, 11 Jun 2015 03:31:40 GMT

DEAR ALL

I have prepared the following P&L in my Pivot Table

DESCRIPTION	ACTUAL	BUDGET	Act Ratio	Bud_Ratio
SALES	100,000	150,000
COST OF SALES	40,000	70,000	40	47
GROSS_PROFIT	60,000	80,000	60	53
STAFF_EXPENSES	10,000	15,000	10	10
ADMIN_EXPENSES	2,000	3,000	2	2
SELLI_EXPENSES	4,000	6,000	4	4
FINAN_EXPENSES	1,000	1,500	1	1
TOTAL_EXPENSES	7,000	10,500	7	7
OTHER INCOME	3,000	4,500	3	3
NET_PROFIT	50,000	65,000	50	43

I have prepared only the 1st three columns and want calculate the two Ratio columns highlighted in yellow.

Formula for calculation all Description Rows(except) are divided by Sales Row.In other words I want to show the ratio against Sales.

Pls help me to write the expression for the same

SAGARA

Re: P & L RATIO

shanemichelon — Thu, 11 Jun 2015 04:31:56 GMT

Act ratio:

=Sum(ACTUAL) / Sum({$ <DESCRIPTION={"SALES"}>} Total ACTUAL)

Budget ratio:

=Sum(ACTUAL) / Sum({$ <DESCRIPTION={"SALES"}>} Total BUDGET)

The set analysis limits the expression to only the SALES records. The total keyword extends the sum across all the rows of the table (therefore picking up the SALES row.

Re: P & L RATIO

sagaraperera — Thu, 11 Jun 2015 07:29:05 GMT

thanks dear it is done

sagara

Re: P & L RATIO

shanemichelon — Thu, 11 Jun 2015 22:37:52 GMT

If you are happy with my Answer, please mark my response as the correct answer