topic Calculating total margin after increase per position in QlikView

Calculating total margin after increase per position

avantime — Mon, 19 Sep 2022 13:44:55 GMT

Hi,

I can`t get a solution for this problem, maybe you can help me. This has to be done in an expression, not at script level, the variables are changed using input boxes inside the application.

PRODUCT	PRODUCT VARIANT	Revenue	COGS	Margin	Margin + Factor	COGS after margin increase	Variables
A	A1	100	50	50.00%	52.00%	48	Factor1	2.00%
A	A2	312	32	89.74%	91.74%	25.76	Factor1	2.00%
A	A3	423	56	86.76%	88.76%	47.54	Factor1	2.00%
A	A4	332	44	86.75%	88.75%	37.36	Factor1	2.00%
B	B1	250	87	65.20%	70.20%	74.5	Factor2	5.00%
B	B2	228	54	76.32%	81.32%	42.6	Factor2	5.00%
C		225	25	88.89%	95.89%	9.25	Factor3	7.00%
D		325	56	82.77%	84.77%	49.5	Factor4	2.00%
E		120	44	63.33%	67.33%	39.2	Factor5	4.00%
		2315	448	80.65%	?????????????	?????????????

How would you calculate the total increased margin?

Thank you

Re: Calculating total margin after increase per position

avantime — Tue, 20 Sep 2022 08:48:22 GMT

Thanks Jack, I know what margin is and how to calculate it, my problem is finding the expression to calculate total margin after increase (for forecasting purposes).

Right now I have something like:

((Sum(Revenue)-Sum(COGS))/Sum(Revenue)+

avg(Aggr(Sum(DISTINCT
IF(Product = 'A, Factor1/100,
IF(Product = 'B', Factor2/100,
IF(Product = 'C', Factor3/100,
IF(Product = 'D', Factor4/100,
IF(Product = 'E', Factor5/100))))))
,PRODUCT))

While the calculation is correct for each product, I do not have a total because of the part of the script involving factors.

I also tried doing an AGGR with the entire script inside of it but I didn`t get the desired result.

Re: Calculating total margin after increase per position

avantime — Thu, 22 Sep 2022 09:36:57 GMT

no one?

Re: Calculating total margin after increase per position

avantime — Thu, 22 Sep 2022 12:19:01 GMT

I found the solution myself after all.

I changed the formula above into:

(Sum(Revenue) - Sum(Cost) +

Sum(Aggr(Sum(Revenue) *

IF(Product = 'A, Factor1/100,
IF(Product = 'B', Factor2/100,
IF(Product = 'C', Factor3/100,
IF(Product = 'D', Factor4/100,
IF(Product = 'E', Factor5/100),PRODUCT))

/ Sum(Revenue)