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

    how to allocate these amounts

    Hans kleijnen

      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 codeRegionregion allocation
      NN001REG A20%
      NN001REG B80%
      NN002REG A100%
      NN003REG B30%
      NN003REG A70%
      NN004REG B25%

       

       

      Table 2:
      DateStaff codeAmount
      31-1-2018NN001                   100
      31-1-2018NN002                     30
      31-1-2018NN003                   200
      31-1-2018NN004                     70
      28-2-2018NN001                     20
      28-2-2018NN002                     50
      28-2-2018NN003                   300
      28-2-2018NN004                   150
      31-3-2018NN001                   179
      31-3-2018NN002                   194
      31-3-2018NN003                   208
      31-3-2018NN004                   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
      DateStaff codeAmountReg AReg B
      31-1-2018NN001                   100                      20               80
      31-1-2018NN002                     30                      30                -  
      31-1-2018NN003                   200                    140               60
      31-1-2018NN004                     70               18
      28-2-2018NN001                     20 4               16
      28-2-2018NN002                     50                      50                -  
      28-2-2018NN003                   300                    210               90
      28-2-2018NN004                   150               38
      31-3-2018NN001                   179                      36             143
      31-3-2018NN002                   194                    194                -  
      31-3-2018NN003                   208                    146               62
      31-3-2018NN004                   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?

       

      thank you in advance

      brgrds

      Hans