Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
I happen to answer my own question:
sum
(aggr( noDISTINCT(([Average Cost])),[Item ID]))If you create a chart (e.g. straight table) with dimension Aging, all you need is a simple expression sum(Cost)
Regards
Michael
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
No matter what chart type is. Pivot is fine. See attached.
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
I happen to answer my own question:
sum
(aggr( noDISTINCT(([Average Cost])),[Item ID]))