Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
May be someone else can help..thanks
How can I do that?
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 |
What do you see when you use this as an expression
Sum(TOTAL [Qty])
I see the value 29056104 for all rows
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
Please find attached my app.
Thank @Vijay Vira, for Sunny Talwars link very helpful.
regards
Mina
You were using OrderQty for your Total Units... Try this
Sum([OrderQty])/Sum(TOTAL [OrderQty])