topic Re: partial subtotal in pivot table in QlikView

partial subtotal in pivot table

Anonymous — Wed, 03 Jan 2018 09:46:33 GMT

Dear all,

I've met a qlikview problem. Anyone could help?

I set a subtotal base on the field of col1 but I only need the subtotal of A and B. how could I hide the subtotal of C?

Many thanks.

Re: partial subtotal in pivot table

Anil_Babu_Samineni — Wed, 03 Jan 2018 11:43:25 GMT

We don't have future for Sub total like =Not WildMatch(col1,'C'). Would you like to go work around?

Re: partial subtotal in pivot table

luismadriz — Wed, 03 Jan 2018 11:50:11 GMT

Hi,

This is a workaround for that,

This is what I loaded:

load * inline [

col1,col2,value

A,a,1

A,b,1

B,c,1

B,d,1

B,e,1

B,f,1

C,g,1

C,h,1

C,i,1

C,k,1];

load * inline [

Dim

2];

Then a pivot table with

first dimension

col1

second dimension

=if(Dim=1,col2,if(col1<>'C','subtotal'))

Measure

sum(value)

don't enable Totals

And this is what it looks like:

I hope this helps,

Cheers,

Luis

Re: partial subtotal in pivot table

Anil_Babu_Samineni — Wed, 03 Jan 2018 11:53:51 GMT

Or, this for second expression

=If(col1='A' or col1 = 'B',Pick(Dim, col2, 'Sub Total'), col2)

Re: partial subtotal in pivot table

tresB — Wed, 03 Jan 2018 11:53:51 GMT

Try like:

If (Dimensionality()=1,if( Col1<>'C', Sum(Value)), sum(Value))

See, in my sample 'Ajith C' doesn't have sub total:

Re: partial subtotal in pivot table

luismadriz — Wed, 03 Jan 2018 12:18:03 GMT

Maybe QSense is lacking this ability but I tried it and couldn't get rid of the word Total. for 'C'. I also didn't include null values

Is there anything else you did in QView on the properties to remove the word Totals?

Cheers

Luis

Re: partial subtotal in pivot table

tresB — Wed, 03 Jan 2018 12:31:19 GMT

Only 'Suppress Zero' is what extra I needed in qv. Did not try in QSense.

Re: partial subtotal in pivot table

Anonymous — Wed, 03 Jan 2018 12:43:32 GMT

‌that’s Cool. Thank u so much.

BTw, could I know where to input the expression (if blala)?