Sum of amount

[#1]

Hi Everyone,

I have two fields Department1 and Department 2 with the same field values. The tabel looks like this

Department1	Amount
A	10
B	20
C	12
D	13
E	15

Department 2	Amount
A	12
B	12
C	14
D	32
E	26
F	25
G	12
H	19
I	18

My requirement is to get the rolled up amount for departments in Department1 from the amount associated with it department2.

For example in Department1 (A+B+C+D+E)= (10+20+12+13+15)= 70 but it should be (12+12+14+32+26) = 96 i.e. the amounts from department2 for each department in department 1.

How can i do this ?

[puttemans]

Do you want to keep both tables separate or not? Is the department 1 & 2 important to keep?

[#3]

I dont fully understand where you want to see the results(in which object) but my quick suggestion -

Why dont you joint the two tables and rename the amounts to Amount1 and Amount2 and then do your analysis. I think it will be easier to accomplish your requirement if its one table.

Please check out the attached doc.

Thanks

AJ

[#4]

I don't want to keep any table. I just showed how my data is. I just have to get one number in a text object for which department is deartment1 but it takes the corresponding amount in department 2 field.

Thanks

[maxgro]

result

script

Tab1:

LOAD * INLINE [

Department1, Amount1

A, 10

B, 20

C, 12

D, 13

E, 15

];

Tab2:

LOAD * INLINE [

Department2, Amount2

A, 12

B, 12

C, 14

D, 32

E, 26

F, 25

G, 12

H, 19

I, 18

];

table chart with

dimension          Department1

expression          sum(if(Department2=Department1, Amount2))

[chiru_thota]

Check attached QVW. I guess It can be done using calculated dimension.

Thanks,

Chiru.

[Anonymous]

Table1:

load Department1,

Amount

from table1;

table2:

noconcatenate

load Department2 as Department1,

Amount

from table2

where Exits(Department1,Department2)

;

drop table1;

do the sum in your chart from table 2 so that you will get correct results.

[#8]

Thanks for the reply everyone. But one complication here is if i make any selection then C and D might get filtered out from Department 2 but will be there in Department 1. If i use the above logic then i won't get any value for C in Department 1 becuase it is filtered out in Department 2.

I want value for C even if it is filtered out in Department 1

I know its getting confusing but the problem is like this.

Thanks,

Sumit 

[maxgro]

sum({$ <Department2=>} if(Department2=Department1, Amount2))

[#10]

Did not work