topic Re: Subtotals in Qlik Vizlib pivot table in App Development

Subtotals in Qlik Vizlib pivot table

AV1 — Thu, 08 Aug 2024 09:30:17 GMT

Hi All,

I'm trying to show customized total with dimension value but unable to do it.

The scenario I'm trying to achieve is.

Sample:
Load * Inline [
Col1, Col2, Col3
1,A,100
1,B,200
1,C,300
1,D,400
2,A1,100
2,B1,200
2,C1,300
2,D1,400
3,A2,100
3,B2,200
3,C2,300
3,D2,400
];

In place of Total, I need to show Col1 Total, Col2 Total etc. after each dimension value ends. Need all of your support on this.

Re: Subtotals in Qlik Vizlib pivot table

Or — Thu, 08 Aug 2024 10:12:46 GMT

You already seem to be doing exactly what you described? Each member of Col1 is ended by a Total line.

If you're referring to having the member in the Totals line, I don't think you can do that, since the Totals name is calculated once for the entire table, rather than individually for each instance.

Re: Subtotals in Qlik Vizlib pivot table

AV1 — Thu, 08 Aug 2024 10:13:05 GMT

Yes, I need to add a text like 'Total1','Total2' after each dimension value instead of just adding Total at the last.

Similar like below.

Re: Subtotals in Qlik Vizlib pivot table

Kushal_Chawda — Thu, 08 Aug 2024 11:45:42 GMT

@AV1 I don't think you can exactly replicate it, but try this below method. Only problem is that we are not using default total option of column, hence you cannot represent it as bold but you can colour it, may be if you can bold it with vizlib. Note: I have done this with default pivot table not vizlib

create one inline table as below

Load * Inline [
Dim
1
2 ];

Create Pivot table with below dimensions

1. Col1 : =Pick(Dim,Col1,'Total '&Col1)

Text colour expression for Col1 : =if(wildmatch(Pick(Dim,Col1,'Total '&Col1),'Total*'),LightBlue())

2. Col2 : =Pick(Dim,Col2,'Sum')

Text colour expression for Col2 : =if(Pick(Dim,Col2,'Sum')='Sum',LightBlue())

Col3 Measure:

=pick(Dim,
Sum(Col3),
Sum(total <Col1>Col3))

Col3 Measure Text colour expression : =if(Pick(Dim,Col2,'Sum')='Sum',LightBlue())

In sorting option, Sorting Col1 by expression : =Pick(Dim,Col1,Col1). Remove the sort on Col2

Re: Subtotals in Qlik Vizlib pivot table

Or — Thu, 08 Aug 2024 11:52:36 GMT

As far as I know, not possible in the native pivot table, unfortunately. You could do something clunky with adding extra dimension values, using ValueList() somehow, etc. but not anything that would just be toggled on or off.