How to aggregate the costs obtained from linked table?

[#1]

Hi All,

I linked two tables by item ID and trying to get the item cost for main table from the other table.

The Main table is like:                                                 The 2nd Table is like:

Item ID     Aging                                                                 Item ID      Cost

a               2                                                                         a                 $10

b               3                                                                         b                 $5

c               2                                                                         c                  $3

d               2                                                                         d                 $5

e               4                                                                         e                 $10

f                3                                                                          f                  $5

My output table should be look like:

Aging     Total Cost

2               $10+$3+$5 = $18

3               $5+$5 = $10

4               $10

With my current calculation:  sum (aggr( noDISTINCT((Cost)),Aging, CURRENT_AREA)) 

Some rows of "Total Cost" are missing, for example the "Total Cost" for "Aging" of 2 is $0 instead of $18. Anyone knows how to fix it?  (CURRENT_AREA is another dimension - for each CURRENT_AREA I am doing this aggregation)

Thanks in advance!!

[Anonymous]

If you create a chart (e.g. straight table) with dimension Aging, all you need is a simple expression sum(Cost)

Regards

Michael

[#3]

Thank you but I should have mentioned I am using Pivot table... And If I am just using sum(Cost) it would give me only the single item's cost not the summed one

[Anonymous]

No matter what chart type is.  Pivot is fine.  See attached.

[#5]

The 2nd Column is I used the "VLookup" function in excel to get the cost information before loading the file into Qlikview, the 3rd column is when I used linked table to read in cost. Ideally the two columns should match. The 4th column is just using Sum(Average Cost)

I just realize that for the 3rd column, when there's just single item under the corresponding Aging# then the cost shows

in the row. Oherwise it returns 0

[#6 · Accepted solution]

I happen to answer my own question:

sum

(aggr( noDISTINCT(([Average Cost])),[Item ID]))