topic Re: Percentage Calculation in QlikView

Percentage Calculation

Fri, 27 Jun 2014 13:52:41 GMT

Hi,

Please could somebody help me with a percentage calculation?

I am busy with What If analysis and Input boxes and have the below scenario:

I have 4 products with a Sales Price. The user is allowed to enter a Discount% into an Input box. I then calculate the Fee after Discount. Each of these products have a Payback (Cost of Sale). I then calculate the Margin which is the Sales Price - Discount% - Payback. After this I calculate the Margin%. It calculates the Margin% per product correctly, but my problem is with the Total Margin% which is the 10% in red. It should actually be the 31% in green. It looks like it does the 20% Discount on the Total Sales Price and calculates the Margin% from there. Just below the table you can see the calculations I use. What should I change so it would use the Total Margin (4300.2) / Total Payback (13860) to get the 31%

Product	Sales Price	Discount%	Fee after Discount	Payback	Margin	Margin%	Header
TOTAL	19116		18160.2	13860	4300.2	10%	31%
A	4779		4779	3465	1314	38%
B	4779		4779	3465	1314	38%
C	4779	20	3823.2	3465	358.2	10%
D	4779		4779	3465	1314	38%

Sales Price = sum(ExchangeFee*vSalesPctChange)

Discount% = InputSum(UnitPriceInputDiscount)

Fee after Discount = sum(ExchangeFee*vSalesPctChange)*(1-(Discount%/100))

Payback = sum(PaybackAmount*vCostPctChange)

Margin = [Fee after Discount] - sum(PaybackAmount*vCostPctChange)

Margin% = [Margin]/sum(PaybackAmount*vCostPctChange)

Thanks

Antoinette

Re: Percentage Calculation

Fri, 27 Jun 2014 14:08:33 GMT

what is PaybackAmount and vCostPctChange

why dont you just use [Margin]/[Payback]

Re: Percentage Calculation

Greg_Williams — Fri, 27 Jun 2014 14:16:02 GMT

One can use Column(1) / Column(2) ... adding some more logic might be required for your exact calculation...but easy way to create a calculation. Otherwise - perhaps using some set analysis might be required?

There is a "Relative" option in the Expression tab in the chart properties that might be of use to you.

Re: Percentage Calculation

Fri, 27 Jun 2014 14:29:06 GMT

I tried [Margin]/[Payback] and it stills gives me the 10%

Re: Percentage Calculation

Fri, 27 Jun 2014 14:35:16 GMT

Hi,

I've tried Column(1)/Column(2) and the relative option also doesn't work in my scenario.

Can you maybe give me an example of the set analysis?

Re: Percentage Calculation

Greg_Williams — Fri, 27 Jun 2014 14:43:19 GMT

Have you played with the Aggregation on the expression in the expression's tab? "Total Mode"

Set Analysis: syntaxes, examples

Re: Percentage Calculation

Fri, 27 Jun 2014 15:39:28 GMT

If you are using a Straight Table, you can try to change the Total Mode for the Margin% to Average. You can find the Total Mode by going to the Expressions tab, selecting Margin% and changing the Total Mode.

Re: Percentage Calculation

Mon, 30 Jun 2014 11:26:10 GMT

Hi,

Thanks for the useful doc. I will work through it.

Re: Percentage Calculation

Mon, 30 Jun 2014 11:27:15 GMT

Thank you for this. It looks like it is working.