Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

First row in group as subtotal in pivot

Let's say I have a pivot table with subtotal like this (assume Expr in this example is a simple Sum, subtotals in italics):

Dim1Dim2Dim3Expr
A  14
AX 7
AX13
AX24
AX 8
AY36
AY42
B  16
BX 10
BX17
BX23
BX 6
BY31
BY45


If I want to show first value of each group at the lowest grain level as the subtotal, what would be the best way to achieve this, preferably without having to touch the data model? I'm trying various combinations of Aggr() and inter-record functions (e.g. Top() ), but so far nothing works fully.

For reference, the final pivot table should look like this:

Dim1Dim2Dim3Expr
A  3
AX 3
AX13
AX24
AX 6
AY36
AY42
B  7
BX 7
BX17
BX23
BX 1
BY31
BY45

 

Any input will be greatly appreciated.

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Contributor II
Contributor II

Nevermind I think I've got it Smiley Happy

For the record, formula I was looking for is this:

FirstSortedValue( 
	Aggr(
		Sum(Expr),
		(Dim1,(TEXT, ASCENDING)), (Dim2, (TEXT, ASCENDING)), (Dim3, (NUMERIC, ASCENDING))
	),
	Aggr(
		RowNo(TOTAL),
		(Dim1,(TEXT, ASCENDING)), (Dim2, (TEXT, ASCENDING)), (Dim3, (NUMERIC, ASCENDING))
	)
)

This assumes sort order in the table will remain constant, but I can live with that.

View solution in original post

2 Replies
Highlighted

You would probably need to use Dimensionality here. May be try playing around with it a little, else share a sample where you are trying to use this and we should be able to help you better

Highlighted
Contributor II
Contributor II

Nevermind I think I've got it Smiley Happy

For the record, formula I was looking for is this:

FirstSortedValue( 
	Aggr(
		Sum(Expr),
		(Dim1,(TEXT, ASCENDING)), (Dim2, (TEXT, ASCENDING)), (Dim3, (NUMERIC, ASCENDING))
	),
	Aggr(
		RowNo(TOTAL),
		(Dim1,(TEXT, ASCENDING)), (Dim2, (TEXT, ASCENDING)), (Dim3, (NUMERIC, ASCENDING))
	)
)

This assumes sort order in the table will remain constant, but I can live with that.

View solution in original post