Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
G'day
in my company each sales branch got his own targets and workers getting bonus for all their sales only if their home branch achieved his target.
For example
DATA
worker | home_branch | sales_sum | |
---|---|---|---|
1 | A | A | 500 |
2 | A | B | 600 |
3 | A | A | 200 |
1 | B | A | -100 |
2 | B | B | 1500 |
4 | B | B | 900 |
I am trying to get the total_sales_branch with out any luck so far.
worker | home_branch | workers_sales | total_sales_branch | calculation | |
---|---|---|---|---|---|
1 | A | 400 | 1300 | (500+600+200) | |
2 | B | 2100 | 2300 | (900+1500-100) | |
3 | A | 200 | 1300 | (500+600+200) | |
4 | B | 900 | 2300 | (900+1500-100) |
any help or direction will be appreciated
Thanks
Yaniv
Maybe like attached?
edit: Here the script:
T1:
LOAD *, recno() as ID INLINE [
worker, sales_branch,home_branch,sales_sum
1, A, A, 500
2, A, B, 600
3, A, A, 200
1, B, A, -100
2, B, B, 1500
4, B, B, 900
];
T2:
LOAD ID, sales_sum Resident T1;
T3:
LOAD ID, sales_branch Resident T1;
T4:
LOAD ID, worker, home_branch Resident T1;
drop table T1;
In UI, create a straight table chart with dimensions worker and home_branch, then two expressions:
=sum(sales_sum)
=sum(aggr(if(sales_branch = home_branch, sum(total<sales_branch> sales_sum)), sales_branch, worker))
This expression should give you the result:
=if(home_branch = 'A',
sum(total {<sales_branch = {A}>} sales_sum),
sum(total {<sales_branch = {B}>} sales_sum)
)
but it is hard coding the connection from home_branch to total_sales_branch.
Is the first table in your post how the table in your data model looks? Or is it possible to remodel your data?
Thanks swuehl
I have about 70 branches so it will be impossible to code it that way.
The first table shows the main idea of the data model as the original contain many more fields. in what way did you think to remodel it?
Maybe like attached?
edit: Here the script:
T1:
LOAD *, recno() as ID INLINE [
worker, sales_branch,home_branch,sales_sum
1, A, A, 500
2, A, B, 600
3, A, A, 200
1, B, A, -100
2, B, B, 1500
4, B, B, 900
];
T2:
LOAD ID, sales_sum Resident T1;
T3:
LOAD ID, sales_branch Resident T1;
T4:
LOAD ID, worker, home_branch Resident T1;
drop table T1;
In UI, create a straight table chart with dimensions worker and home_branch, then two expressions:
=sum(sales_sum)
=sum(aggr(if(sales_branch = home_branch, sum(total<sales_branch> sales_sum)), sales_branch, worker))
Hi,
one possible solution could be also:
tabInput:
LOAD RecNo() as %salesID, *
FROM [https://community.qlik.com/thread/165987] (html, codepage is 1252, embedded labels, table is @2);
tabSales:
LOAD %salesID,
sales_branch,
sales_sum
Resident tabInput;
tabWorkers:
LOAD Distinct
AutoNumber(worker,'worker') as %workerID,
worker,
home_branch
Resident tabInput;
tabBranchesTemp:
CrossTable (branchTypeTemp, branch)
LOAD %salesID,
sales_branch,
home_branch
Resident tabInput;
Left Join (tabBranchesTemp)
LOAD Distinct
branchTypeTemp,
SubField(branchTypeTemp,'_',1) as branchType
Resident tabBranchesTemp;
Left Join (tabBranchesTemp)
LOAD Distinct
branch,
branchType,
AutoNumberHash128(branch, branchType) as %branchID
Resident tabBranchesTemp;
tabBranches:
LOAD Distinct
%branchID,
branch,
branchType
Resident tabBranchesTemp
Order By branch, branchTypeTemp;
tabLink:
LOAD %salesID,
AutoNumber(worker,'worker') as %workerID
Resident tabInput;
Left Join (tabLink)
LOAD %salesID,
%branchID
Resident tabBranchesTemp;
DROP Tables tabInput, tabBranchesTemp;
hope this helps
regards
Marco
Referring to my previous post, maybe I was too tired (or maybe I am not really awake right now?):
I think the expression for total_sales_branch also works with your original data model, no need for scripting.