Sales Ratio

[upaliwije]

Hi Friends

I have following sales data in a worksheet

Dept	Prd	Amount
1	A	2000
1	B	1000
1	C	3000
1	D	4000
2	A	2500
2	B	1500
2	D	5000
2	E	6000
3	E	3500
3	D	8500
3	C	1000
3	B	5000

When I load data I want to modify the script to have another column in the table showing the % of sales of each Prd in each dept

In other words my table view should show data in following format. Pls help me to modify the script to obtain what I want . I have attached my QVW pls

Dept	Prd	Amount	Ratio
1	A	2000	20%
1	B	1000	10%
1	C	3000	30%
1	D	4000	40%
2	A	2500	17%
2	B	1500	10%
2	D	5000	33%
2	E	6000	40%
3	E	3500	19%
3	D	8500	47%
3	C	1000	6%
3	B	5000	28%

[MK_QSL · Accepted solution]

If you want to do this at back end use this..

Data:

LOAD * Inline

[

  Dept,Prd,sales,

  1, A, 2000

  1, B, 1000

  1, C, 3000

  1, D, 4000

  2, A, 2500

  2, B,  1500

  2, D, 5000

  2, E, 6000

  3, E, 3500

  3, D, 8500

  3, C, 1000

  3, B, 5000

];

Left Join (Data)

Load Dept, SUM(sales) as TotalSales Resident Data Group By Dept;

Left Join (Data)

Load Dept, Prd, Num(sales/TotalSales,'#0%') as ratio Resident Data;

If you don't want to change script and want to do it at front end.. use this

create a straight or pivot table

Dimension

Dept

Prd

Expression

SUM(sales)

SUM(sales)/SUM(Total <Dept>sales)

[tresesco]

PFA

[MK_QSL]

In case, if you have same product more than once in same dept.. use as below..

Data:

LOAD * Inline

[

  Dept,Prd,sales,

  1, A, 500

  1, A, 1500

  1, B, 1000

  1, C, 3000

  1, D, 4000

  2, A, 2500

  2, B,  1000

  2, B,  500

  2, D, 5000

  2, E, 6000

  3, E, 2000

  3, E, 1500

  3, D, 8500

  3, C, 1000

  3, B, 5000

];

Left Join (Data)

Load Dept, SUM(sales) as TotalDeptSales Resident Data Group By Dept;

Left Join (Data)

Load Dept, Prd, SUM(sales) as TotalDeptPrdSales Resident Data Group By Dept, Prd;

Left Join (Data)

Load Dept, Prd, sales, Num(TotalDeptPrdSales/TotalDeptSales,'#0%') as Ratio Resident Data;

[upaliwije]

Thanks a lot to both of for your prompt reply. Both answers are correct

[upaliwije]

In a further Development I want to apportion the following expenses among the Prds and the output table should be like given below

Dept	Expense	amount	Prd	Ratio	Exp_amount
1	salary	2000	A	20%	400
1	salary	2000	B	10%	200
1	salary	2000	C	30%	600
1	salary	2000	D	40%	800
1	rent	5000	A	20%	1,000
1	rent	5000	B	10%	500
1	rent	5000	C	30%	1,500
1	rent	5000	D	40%	2,000
2	salary	4000	A	17%	667
2	salary	4000	B	10%	400
2	salary	4000	D	33%	1,333
2	salary	4000	E	40%	1,600

Pls Help

[tresesco]

May be :

Sum(Ratios)*Sum(sales)/100

[upaliwije]

Could you please explain me how to load expenses into data model

[tresesco]

What is the logic of defining expense type?

[upaliwije]

name of Expense Like Salary, Rent, Travelling. They are in a separate table

Dept	Expense	amount
1	salary	2000
1	rent	5000
2	salary	4000
3	salary	1000
3	rent	2500
3	travelling	3500