How to sum all records when grouped by a primary dimension, but only show sum on last record of the grouped dimension...

[#1]

I am trying to get my expression in a strait table to look like the last column in the image below. Any ideas on how to do this with inter record functions?

Thanks.

Product	Production Location	Inventory Cases	Inventory Cases Sum Grouped By Production Location
12345	100	100
12345	100	150
12345	100	75	325
12345	200	30	30
12345	300	200
12345	300	180
12345	300	530
12345	300	400	1310

[#2 · Accepted solution]

I'm not sure how you could only show the value once without inter-record functions.

You can use the following expression to get the total on each line:

Sum(TOTAL <Product, [Production Location]> [Inventory Cases])

EDIT: I spoke too soon, you can use an Aggr function to get the value on only one line, but is seems to go on the first record:

Aggr(Sum([Inventory Cases]), Product, [Product Location])

[#3]

Thanks. I tried the Aggr but it just returns nulls.

[#4]

Did the TOTAL Sum work? It could be the field names (I just guessed at your field names). Try using only one dimension in the Aggr and see if you get anything.

Sometimes Aggr can be a bit tricky, but it's hard to troubleshoot without the data.

[prabhu0505]

Also you can script for doing this.

load product, product location, sum(Inv_Cash) from table;

I don't know whether this is optimal or not.

[prabhu0505]

<blockquote><pre>
select product, product location, sum(Inv_Cash) from table group by product, product location;

[fernandotoledo]

Use the expression sum(total <Product> InventoryCases) that will repeat the total in every row for the same product.

then change the font color in the expression column with this expression if(below(Product,1)=Product,white(),black())

This will show the value in the last row and hide the others

[#8]

NMiller,

To test the Aggr I built some sample data in Excel, then loaded into test QVW, and tried your method. It worked (see below). I think the problem with my live date that I have two many dimensions from multiple tables. But I think you have pointed me in the right direction. Thanks for the help.

SKU	Branch_Plant	Order_Num	=sum(Inventory_Lbs)	Sum(TOTAL <SKU, [Branch_Plant]> [Inventory_Lbs])	Aggr(Sum([Inventory_Lbs]), SKU, [Branch_Plant])
			2450000	2450000	-
12345	708001	3	50000	550000	-
12345	708001	1	200000	550000	550000
12345	708001	2	300000	550000	-
12345	708002	4	200000	400000	400000
12345	708002	5	200000	400000	-
12345	708003	8	150000	1100000	-
12345	708003	6	450000	1100000	1100000
12345	708003	7	500000	1100000	-
12345	708004	9	250000	250000	250000
12345	708005	11	50000	150000	-
12345	708005	10	100000	150000	150000

[#9]

Thanks Saravana. I was just trying to do this in the object though.

[#10]

Aggr worked.... Thanks.

SKU	Branch_Plant	Order_Num	=sum(Inventory_Lbs)	Sum(TOTAL <SKU, [Branch_Plant]> [Inventory_Lbs])	Aggr(Sum([Inventory_Lbs]), SKU, [Branch_Plant])
			2450000	2450000	-
12345	708001	3	50000	550000	-
12345	708001	1	200000	550000	550000
12345	708001	2	300000	550000	-
12345	708002	4	200000	400000	400000
12345	708002	5	200000	400000	-
12345	708003	8	150000	1100000	-
12345	708003	6	450000	1100000	1100000
12345	708003	7	500000	1100000	-
12345	708004	9	250000	250000	250000
12345	708005	11	50000	150000	-
12345	708005	10	100000	150000	150000