Adding calculated column to concatenated tables

[#1]

Hello, all.

I am struggling with how to add columns to concatenated tables in order to calculate deltas between columns.

So here is a simplified example of what I want:

Table A has Category and Spend for Month A
Table B has Category and Spend for Month B

What I want is to create a table box object with the following:

Category, Spend for Month A, Spend for Month B, Delta

I have everything but the Delta part using the following code:

Month1:

sql select category, month_a_spend
from month_a_table

Month2:

sql select category, month_b_spend
from month_b_table

This gives me everything but the Detla column.  In SQL, i would do this by joining Month1 and Month2 and adding calculations as columns.  And I tried to achieve this with QlikView but couldn't and none of the examples I found work. 

Help, please.  Many thanks in advance.

[jpapador · Accepted solution]

You could do it in the script but it would be faster to use a straight table instead of a table box.  Make your dimension category and you expressions:

1. Sum({$<Month={'A'}>}Spend)

2. Sum({$<Month={'B'}>}Spend)

3. Sum({$<Month={'A'}>}Spend) - Sum({$<Month={'B'}>}Spend)

That will create the table you are looking for

[#3]

Thanks!  That did it!! 

[43918084]

• THANKS. 
• may I know where I should put the calculated script?
• Load
• A. As t1.a
• From t1
•  
• Load
• A as t1.a
• From t2