total calculation

[egoziyan]

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

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

[swuehl]

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?

[egoziyan]

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?

[swuehl · Accepted solution]

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))

[MarcoWedel]

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

[swuehl]

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.