9 Replies Latest reply: Sep 14, 2017 2:14 PM by Eduardo DImperio

# how to allocate these amounts

Hi All,

I have employees allocated to a region (Table 1). I have total expenses per employee per month (Table 2). Needed output is expenses per employee per region.

 Table 1: Staff code Region region allocation NN001 REG A 20% NN001 REG B 80% NN002 REG A 100% NN003 REG B 30% NN003 REG A 70% NN004 REG B 25%

 Table 2: Date Staff code Amount 31-1-2018 NN001 100 31-1-2018 NN002 30 31-1-2018 NN003 200 31-1-2018 NN004 70 28-2-2018 NN001 20 28-2-2018 NN002 50 28-2-2018 NN003 300 28-2-2018 NN004 150 31-3-2018 NN001 179 31-3-2018 NN002 194 31-3-2018 NN003 208 31-3-2018 NN004 222

The required output in this example would be like below. Total amount per employee is allocated to the region based on the percentage of table 1

 Region Date Staff code Amount Reg A Reg B 31-1-2018 NN001 100 20 80 31-1-2018 NN002 30 30 - 31-1-2018 NN003 200 140 60 31-1-2018 NN004 70 18 28-2-2018 NN001 20 4 16 28-2-2018 NN002 50 50 - 28-2-2018 NN003 300 210 90 28-2-2018 NN004 150 38 31-3-2018 NN001 179 36 143 31-3-2018 NN002 194 194 - 31-3-2018 NN003 208 146 62 31-3-2018 NN004 222 56

could someone explain me how to do this, or even better (if it is a simple excercise) to create a .qvw based on these data?

brgrds

Hans

• ###### Re: how to allocate these amounts

On UI part or in Back end you required to do this.

• ###### Re: how to allocate these amounts

Check the attached App

• ###### Re: how to allocate these amounts

On the base of your solution check the attached also but question here is in second table you have field Amount which has no bifurcation of the region for what it is will it distribute equals to region check the  NN003 example

Out Put

• ###### Re: how to allocate these amounts

Try this:

Table1:

*

FROM

qvdfromtable1

Left Join(Table1)

//Table2:

*

FROM

qvdfromtable2

• ###### Re: how to allocate these amounts

Hi Hans,

may be this

[Table 1]:
LOAD Staff_code, Region, Num(region_allocation) as region_allocation  Inline [
Staff_code, Region, region_allocation
NN001, REG A, 20%
NN001, REG B, 80%
NN002, REG A, 100%
NN003, REG B, 30%
NN003, REG A, 70%
NN004, REG B, 25%
]
;
[Table 2]:
Join
Date Staff_code Amount
31-1-2018 NN001 100
31-1-2018 NN002 30
31-1-2018 NN003 200
31-1-2018 NN004 70
28-2-2018 NN001 20
28-2-2018 NN002 50
28-2-2018 NN003 300
28-2-2018 NN004 150
31-3-2018 NN001 179
31-3-2018 NN002 194
31-3-2018 NN003 208
31-3-2018 NN004 222
]
(delimiter is spaces);
Resident [Table 1];
Drop Table [Table 1];
Generic
Resident [Table 2];
Drop Table
[Table 2];

Regards,

Antonio

• ###### Re: how to allocate these amounts

Hi Hans,

Maybe this straight table?

Date Staff code Amount Reg A Reg B
1723 829.4 562.1
31-3-2018NN00117935.8143.2
31-3-2018NN0021941940
31-3-2018NN003208145.662.4
31-3-2018NN004222055.5
31-1-2018NN0011002080
31-1-2018NN00230300
31-1-2018NN00320014060
31-1-2018NN00470017.5
28-2-2018NN00120416
28-2-2018NN00250500
28-2-2018NN00330021090
28-2-2018NN004150037.5

REG A = Alt(Amount* only({\$<Region = {'REG A'}>}[region allocation]),0)

REG B = Alt(Amount* only({\$<Region = {'REG B'}>}[region allocation]),0)

Regards

Andrew

• ###### Re: how to allocate these amounts

Create either straight or pivot table

Dimension:

Date

Staff Code

Expression:

1) Amount

=sum(Amount)

2) REG A

=sum(Amount)* Only({<Region={'REG A'}>}region_allocation)

3) REG B

=sum(Amount)* Only({<Region={'REG B'}>}region_allocation)

• ###### Re: how to allocate these amounts

Dear all

apparently I was caught in a mind trap. My assumption was that I needed to create a key like NN001_REG_A. Multiple Roads lead to Rome ....

I am sorry, but selecting more answers as "the correct one" is not possible

Thank you all for your support

krgrds Hans

• ###### Re: how to allocate these amounts

We don't do that to get a green star "as correct", we do to help. Choose one and gg