topic Re: Problem calculating correct percentages in QlikView

Problem calculating correct percentages

qlikmpate0 — Tue, 10 Apr 2018 11:04:18 GMT

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

Re: Problem calculating correct percentages

sunny_talwar — Tue, 10 Apr 2018 11:13:13 GMT

How about you try this

Sum([Qty])/Sum(TOTAL [Qty])

Sum([Sales $]/Sum(TOTAL [Sales $])

Re: Problem calculating correct percentages

qlikmpate0 — Tue, 10 Apr 2018 11:20:35 GMT

Thanks Sunny this gives an incorrect % value for both units and sales

Re: Problem calculating correct percentages

sunny_talwar — Tue, 10 Apr 2018 11:22:57 GMT

Would you be able to share a sample?

Re: Problem calculating correct percentages

sunny_talwar — Tue, 10 Apr 2018 11:34:41 GMT

Script doesn't help... what do you get when you use this?

Sum([Qty])/Sum(TOTAL [Qty])

Re: Problem calculating correct percentages

qlikmpate0 — Tue, 10 Apr 2018 11:48:51 GMT

Class of Trade	No of Customers	Total Units	Total Sales	% Units	% Sales
AGENCY	19	79,728	247,643	.26%	.30%
DECORATOR	70	2,064,481	4,535,474	6.14%	5.10%
DISTRIBUTOR	98	38,485,801	108,168,466	85.92%	89.82%
MANUFACTURER	3	52,531	138,689	.03%	.04%
RETAIL	9	412,017	356,427	1.31%	.41%
WHOLESALE	45	2,433,573	4,398,952	5.29%	3.53%
-	33	328,100	675,878	1.05%	.79%
	277	43,856,231	118,521,528	100.00%	100.00%

Re: Problem calculating correct percentages

sunny_talwar — Tue, 10 Apr 2018 11:52:16 GMT

Can't really say what might be wrong here without looking at your dimensions, expressions and chart settings... may be someone else can help...

Best,

Sunny

Re: Problem calculating correct percentages

shiveshsingh — Tue, 10 Apr 2018 12:00:44 GMT

Can you share the app ?

Re: Problem calculating correct percentages

qlikmpate0 — Tue, 10 Apr 2018 12:10:13 GMT

I only have one dimension and that is the trade all expression attached

Re: Problem calculating correct percentages

qlikmpate0 — Tue, 10 Apr 2018 12:11:42 GMT

Sorry no because too much company info

Re: Problem calculating correct percentages

vvira1316 — Tue, 10 Apr 2018 12:17:33 GMT

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.

Re: Problem calculating correct percentages

shiveshsingh — Tue, 10 Apr 2018 12:19:17 GMT

May be someone else can help..thanks

Re: Problem calculating correct percentages

qlikmpate0 — Tue, 10 Apr 2018 12:21:06 GMT

How can I do that?

Re: Problem calculating correct percentages

qlikmpate0 — Tue, 10 Apr 2018 12:31:59 GMT

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

Re: Problem calculating correct percentages

sunny_talwar — Tue, 10 Apr 2018 12:33:19 GMT

What do you see when you use this as an expression

Sum(TOTAL [Qty])

Re: Problem calculating correct percentages

qlikmpate0 — Tue, 10 Apr 2018 12:37:18 GMT

I see the value 29056104 for all rows

Re: Problem calculating correct percentages

vvira1316 — Tue, 10 Apr 2018 12:37:59 GMT

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

Re: Problem calculating correct percentages

vvira1316 — Tue, 10 Apr 2018 13:08:31 GMT

Preparing examples for Upload - Reduction and Data Scrambling

Re: Problem calculating correct percentages

qlikmpate0 — Tue, 10 Apr 2018 13:25:45 GMT

Please find attached my app.

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

regards

Mina

Re: Problem calculating correct percentages

sunny_talwar — Tue, 10 Apr 2018 13:37:30 GMT

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

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