topic Re: Sales Ratio in QlikView

Sales Ratio

upaliwije — Wed, 25 Jan 2017 06:58:30 GMT

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%

Re: Sales Ratio

MK_QSL — Wed, 25 Jan 2017 07:09:41 GMT

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)

Re: Sales Ratio

tresB — Wed, 25 Jan 2017 07:11:43 GMT

PFA

Re: Sales Ratio

MK_QSL — Wed, 25 Jan 2017 07:19:27 GMT

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;

Re: Sales Ratio

upaliwije — Wed, 25 Jan 2017 08:28:22 GMT

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

Re: Sales Ratio

upaliwije — Wed, 25 Jan 2017 09:40:49 GMT

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

Re: Sales Ratio

tresB — Wed, 25 Jan 2017 09:51:00 GMT

May be :

Sum(Ratios)*Sum(sales)/100

Re: Sales Ratio

upaliwije — Wed, 25 Jan 2017 10:00:49 GMT

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

Re: Sales Ratio

tresB — Wed, 25 Jan 2017 10:04:51 GMT

What is the logic of defining expense type?

Re: Sales Ratio

upaliwije — Wed, 25 Jan 2017 10:20:00 GMT

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

Re: Sales Ratio

tresB — Wed, 25 Jan 2017 10:39:23 GMT

There has to be a KEY to associate. In your sample I don't see a key field. You might have to create a composite key for the same. Go through your tables, I am sure you would get one.