topic Re: total calculation in QlikView

total calculation

egoziyan — Thu, 28 May 2015 20:57:08 GMT

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

500

600

200

-100

1500

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

Re: total calculation

swuehl — Thu, 28 May 2015 22:09:28 GMT

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?

Re: total calculation

egoziyan — Thu, 28 May 2015 22:45:43 GMT

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?

Re: total calculation

swuehl — Thu, 28 May 2015 23:22:47 GMT

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

Re: total calculation

MarcoWedel — Fri, 29 May 2015 00:36:59 GMT

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

Re: total calculation

swuehl — Fri, 29 May 2015 09:12:51 GMT

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.