script for proportion for a certain column

[Energetic]

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%

[petter]

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%?

[Energetic]

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%

[PrashantSangle]

can you give more clarity on proportion logic??

Regards,

[PrashantSangle]

Try expression

sum(Sales) / Sum(Total Sales)

then select show in %

Regards,

[petter]

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=;

[PrashantSangle]

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,

[Anonymous]

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;