Cost Allocation

[colliers_sn]

Hello,

Hope I can get some assistance with putting together a solution to allocate my costs.  Ideally on the script side.

I have costs that I receive monthly that I want to allocate out to various departments based on their headcount.  Sample data below.

Expenses:

Expense Type	Amount
Telecom	5000
Network services	8000

Headcount:

Dept	Headcount
Dept 1	20
Dept 2	30

The goal is to get this result.

Alocated Expenses:

Dept	Expense Type	Amount
Dept 1	Telecom	2000
Dept 1	Network Services	3200
Dept 2	Telecom	3000
Dept 2	Network Services	4800

Any ideas would great be appreciated.

[sunny_talwar · Accepted solution]

Try this:

HeadCount:

LOAD Dept,

     Headcount

FROM

[https://community.qlik.com/thread/216883]

(html, codepage is 1252, embedded labels, table is @2);

Join(HeadCount)

LOAD Sum(Headcount) as Total

Resident HeadCount;

Join(HeadCount)

LOAD [Expense Type],

     Amount

FROM

[https://community.qlik.com/thread/216883]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD Dept,

  [Expense Type],

  Amount * Headcount/Total as Amount

Resident HeadCount;

DROP Table HeadCount;

[colliers_sn]

Hey Sonny....that worked perfectly...thanks so much.

[sunny_talwar]

Great

If you got what you were looking for, please close the thread by marking the correct answer

Qlik Community Tip: Marking Replies as Correct or Helpful