Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

ProductProduction LocationInventory CasesInventory Cases Sum Grouped By Production Location
12345100100
12345100150
1234510075325
123452003030
12345300200
12345300180
12345300530
123453004001310


1 Solution

Accepted Solutions
Not applicable
Author

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])


View solution in original post

12 Replies
Not applicable
Author

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])


Not applicable
Author

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

Not applicable
Author

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
Specialist
Specialist

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
Specialist
Specialist

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

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

Not applicable
Author

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.

SKUBranch_PlantOrder_Num=sum(Inventory_Lbs)Sum(TOTAL <SKU, [Branch_Plant]> [Inventory_Lbs])Aggr(Sum([Inventory_Lbs]), SKU, [Branch_Plant])
24500002450000-
12345708001350000550000-
123457080011200000550000550000
123457080012300000550000-
123457080024200000400000400000
123457080025200000400000-
1234570800381500001100000-
12345708003645000011000001100000
1234570800375000001100000-
123457080049250000250000250000
123457080051150000150000-
1234570800510100000150000150000


Not applicable
Author

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

Not applicable
Author

Aggr worked.... Thanks.

SKUBranch_PlantOrder_Num=sum(Inventory_Lbs)Sum(TOTAL <SKU, [Branch_Plant]> [Inventory_Lbs])Aggr(Sum([Inventory_Lbs]), SKU, [Branch_Plant])
24500002450000-
12345708001350000550000-
123457080011200000550000550000
123457080012300000550000-
123457080024200000400000400000
123457080025200000400000-
1234570800381500001100000-
12345708003645000011000001100000
1234570800375000001100000-
123457080049250000250000250000
123457080051150000150000-
1234570800510100000150000150000