topic script for proportion for a certain column in App Development

script for proportion for a certain column

Energetic — Mon, 03 Sep 2018 03:30:20 GMT

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%

Re: script for proportion for a certain column

petter — Mon, 03 Sep 2018 04:48:50 GMT

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

Re: script for proportion for a certain column

Energetic — Mon, 03 Sep 2018 05:23:00 GMT

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%

Re: script for proportion for a certain column

PrashantSangle — Mon, 03 Sep 2018 05:23:22 GMT

can you give more clarity on proportion logic??

Regards,

Re: script for proportion for a certain column

PrashantSangle — Mon, 03 Sep 2018 05:27:49 GMT

Try expression

sum(Sales) / Sum(Total Sales)

then select show in %

Regards,

Re: script for proportion for a certain column

petter — Mon, 03 Sep 2018 05:45:59 GMT

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

Re: script for proportion for a certain column

PrashantSangle — Mon, 03 Sep 2018 05:59:35 GMT

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,

Re: script for proportion for a certain column

Anonymous — Mon, 03 Sep 2018 10:02:35 GMT

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;