Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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'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
Highlighted
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'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

Highlighted
Not applicable

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

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

Highlighted
Not applicable

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

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.

Highlighted
Specialist
Specialist

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

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.

Highlighted
Specialist
Specialist

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

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

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

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

Highlighted
Not applicable

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

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


Highlighted
Not applicable

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

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

Highlighted
Not applicable

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

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