Linking two tables & achieve the expected output

[nevilledhamsiri]

Dear Experts!

Please refer two excel tables which I need to link & create a table  like the 3rd one to show a simple  profit/loss calculation. Of course the two months revenue & commissions to be converted to running average & to take the latest month running average (Aug) & to be taken to the 3rd table. The figures  in 1st table to be taken as it is.

Thanks in advance

Neville

1st Table

BRANCH     GROSS_PREMIUM      CLAIMS     EXPENSES

RA               2,000,000                     450,000     400000

EH               1,500,000                     500,000     350000

K7               1,700,000                     650,000     250000

K9               1,500,000                     700,000     350000

2nd Table

BRANCH    MONTH    REVENUE    COMMISSION

RA              JUL         2000000         250000

RA              AUG         2200000         350000

EH              JUL         1500000         400000

EH              AUG         1700000         350000

K7              JUL         900000         200000

K9              JUL         750000         150000

K9              AUG         700000         250000

Output should look like below:

BRANCH    GROSS_PREMIUM    RUNNING_REVENUE    RUNNING_COMMISSION    ACTUAL CLAIMS    ACTUAL EXPENSES    PROFIT/LOSS

RA               2,000,000               2100000                       300000                                 450,000     400000                                  950,000

EH               1,500,000               1600000                       375000                                     500,000     350000                             375,000

K7               1,700,000               950000                       225000                                      650,000     250000                             (175,000)

K9                   1,500,000               725000                       200000                                 700,000     350000                             (525,000)

[oscar_ortiz]

Neville,

Maybe something like this?

Good luck

Oscar

[nevilledhamsiri]

Dear Oscar,

Can you please rewrite the expression you used to appear data as such!. Also Since I need to pick only the september running total for the final profit calculaton summery,how it could be achieved. Here I am going to make a table like below

    BR CODE                run_average          run_commission          act_claim          profit/loss

    AS                         1352131               To be linked as revenue     150000          runn-rev-run comm-act claim

     BL                         2654689               Do                                   150000          do

Please help

Neville

[oscar_ortiz]

How did you derive the value of 2654689 for BL?

[oscar_ortiz]

Check the examples on the various sheets.

[nevilledhamsiri]

Dear Mr Oscar,

Very sorry I cannot open it up I have only PE. Could you please write the expression in the doc itself if  I am not troubling you!

Thanks very much for your time!

Neville

[oscar_ortiz]

It's the same expression I just modified the charts slightly.

Good luck

Oscar

[nevilledhamsiri · Accepted solution]

This is true!, I have just said as it is appeared in your working of course it has to be

   2,040,743

How did you derive the value of 2654689 for BL?

Sorry

[oscar_ortiz]

Neville,

Not sure if you solved your issue.  But if you have would you mind closing the thread.

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks

Oscar

[nevilledhamsiri]

Dear Oscar,

Thanks very much, I was able to achieve achieve my output as prescribed by you. Right away I will close the tread

Thank you very much, expecting same response in the future as well!