Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'd like to make a proportion column for sales cloumn.
How can I make a script.
please help me.
Name | Sales | Proportion(%) |
A | 30 | 10% |
B | 40 | 14% |
C | 60 | 21% |
A | 20 | 7% |
B | 10 | 3% |
C | 70 | 24% |
A | 50 | 17% |
B | 40 | 14% |
Which proportion are you aiming to calculate? It seems like you have 110% if you consider all of your data. Shouldn't it be 100%?
It's something wrong sorry for that.
I changed sample data and I'd like to get the sales proportion for each name.
Name | Sales | Proportion(%) |
A | 30 | 11.50% |
B | 70 | 26.90% |
C | 20 | 7.70% |
D | 60 | 23.10% |
E | 40 | 15.40% |
F | 30 | 11.50% |
G | 10 | 3.80% |
can you give more clarity on proportion logic??
Regards,
Try expression
sum(Sales) / Sum(Total Sales)
then select show in %
Regards,
I would recommend to do it like Prashant proposed directly in the UI in a chart, straight table or pivot table and not in the script.
Then you can use name as a dimension and Sum(Sales) / Sum(TOTAL Sales) as expression in a measure.
However if you really need to do it in the script this is a way of doing it:
RAW_DATA:
LOAD
*,
If(RecNo() = 1 , Sales , Peek('AccSum')+Sales) AS AccSum
INLINE [
Name,Sales
A,30
B,40
C,60
A,20
B,10
C,70
A,50
B,40
];
TotSum = Peek('AccSum',-1,'RAW_DATA');
DATA:
LOAD
Name,
Sales,
Num(Sales/$(TotSum),'0%','.',',') AS Prop
RESIDENT
RAW_DATA;
DROP TABLE RAW_DATA;
TotSum=;
All this is not neccessary if you do the calculation on the fly as suggested earlier. An additional disadvantage of doing it in the script is much less flexibility. If the user for instance selectes specific names the total sum will not be adjusted to only the selected names.
RAW_DATA: LOAD *, If(RecNo() = 1 , Sales , Peek('AccSum')+Sales) AS AccSum INLINE [Name,SalesA,30B,40C,60A,20B,10C,70A,50B,40];
TotSum = Peek('AccSum',-1,'RAW_DATA');
DATA:LOAD Name, Sales, Num(Sales/$(TotSum),'0%','.',',') AS PropRESIDENT RAW_DATA; DROP TABLE RAW_DATA;TotSum=;
Nice solution sir.
we can also try like if we need to find proportion w.r.t. all
then
try
Sales_table:
Load * from table;
Join
Load Sum(Sales) as Total_Sales from Table
group by Name;
sales_with_proportion:
Load *, Sum(Sales)/ Sum(Total_Sales) as Proportion
Resident Sales_table
group by Name;
Drop table Sales_table;
Regards,
Hi Sanghyuck
kindly try this
Table:
LOAD Name,
Sales,
'' as temp
FROM
[1.xlsx]
(ooxml, embedded labels, table is Sheet6);
left join(Table)
load
temp,
sum(Sales)as Total
Resident Table Group by temp;
Final:
load
Name,
Sales,
Sales/Total as Proportion
Resident Table;
DROP table Table;