topic Re: Cost Allocation in QlikView

Cost Allocation

colliers_sn — Wed, 25 Nov 2020 16:16:04 GMT

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:

Any ideas would great be appreciated.

sunny_talwar — Fri, 13 May 2016 17:38:51 GMT

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 — Fri, 13 May 2016 20:49:19 GMT

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

sunny_talwar — Sat, 14 May 2016 11:44:42 GMT

Great

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