Problem calculating correct percentages

[qlikmpate0]

I have a straight table, of trade, no of customers and sales and units.

I'm trying to calculate the % of sales and units using this expression, but getting incorrect figures for %Units and %Sales(see below)

this is the expression I'm using for %units - (Sum([Qty])/(sum([Qty - TOTAL - Present])*100))

and for %sales I'm using  (sum([Sales $]/[Sales - TOTAL - Present]))*100

example of %units

class of trade total unit/total units * 100 to get % units

eg 79,728/43856,231 * 100 = 0.182%

Do I need to put it in my script, of calculate as an expression?

Class of Trade	No of Customers	Total Units	Total Sales	% Units	% Sales
AGENCY	19	79,728	247,643	1.49%	2.07%
DECORATOR	70	2,064,481	4,535,474	12.38%	16.86%
DISTRIBUTOR	98	38,485,801	108,168,466	67.51%	69.18%
MANUFACTURER	3	52,531	138,689	.11%	.11%
RETAIL	9	412,017	356,427	.38%	.51%
WHOLESALE	45	2,433,573	4,398,952	6.76%	8.17%
-	33	328,100	675,878	11.36%	3.09%
	277	43,856,231	118,521,528	100.00%	100.00%

so confused on where I'm going wrong can someone relieve my misery.

Thanks in advance.

regards

M

[vvira1316]

For someone to help you may be you can share just masked granular data file without the Qlik app. Also provide what is your expected output.

[shiveshsingh]

May be someone else can help..thanks

[qlikmpate0]

How can I do that?

[qlikmpate0]

these are the results I'm expecting

where %Units = total unit per trade/total units * 100

       % sales - total sales per trade/total sales *100 this is my calculation.

Class of Trade	No of Customers	Total Units	Total Sales	%Units	%sales
AGENCY	19	79,728	247,643	0.18%	0.21%
DECORATOR	70	2,064,481	4,535,474	4.71%	3.83%
DISTRIBUTOR	98	38,485,801	108,168,466	87.75%	91.26%
MANUFACTURER	3	52,531	138,689	0.12%	0.12%
RETAIL	9	412,017	356,427	0.94%	0.30%
WHOLESALE	45	2,433,573	4,398,952	5.55%	3.71%
-	33	328,100	675,878	0.75%	0.57%
	277	43,856,231	118,521,528

[sunny_talwar]

What do you see when you use this as an expression

Sum(TOTAL [Qty])

[qlikmpate0]

I see the value 29056104 for all rows

[vvira1316]

Hi Mina,

I saw the table and have some idea about the calculation you are expecting but this is aggregated data table. It doesn't provide idea about your data model is without your sharing your app. That is fine but in order to replicate and solve the issue it will be nice to have granular information that someone can take put it in QV and come up with formula to see where your calculation may be going wrong.

I was looking for information that Sunny usually shares on how to upload sample files. I should have bookmarked those link.

I'll include here a question that one of the community member has posted but because granular data was available to folks who were trying to help were eventually able to help.

Problem summing an average in pivot table

BR,

Vijay

[vvira1316]

Preparing examples for Upload - Reduction and Data Scrambling

[qlikmpate0]

Please  find attached my app.

Thank @Vijay Vira, for Sunny Talwars link very helpful.

regards

Mina

[sunny_talwar · Accepted solution]

You were using OrderQty for your Total Units... Try this

Sum([OrderQty])/Sum(TOTAL [OrderQty])