topic Re: Pivot table subtotal issue in QlikView

Pivot table subtotal issue

swathimgumte — Wed, 25 Nov 2020 16:16:04 GMT

@hi Folks,

I want the subtotals of the pivot table to be shown only if the values in the dimension row is more then one,

if only one row is there then the sub total row should show.

for eg( refer the image attached) i want to show total row if i have both ICATM & USI in second column.

if i have either of the one then the subtotal column should not show.

Message was edited by: swathii gumte Added sample qwv @SunnyTalwar

Message was edited by: swathii gumte Updated sample doc with reduced data

Re: Pivot table subtotal issue

sunny_talwar — Wed, 25 Apr 2018 13:10:26 GMT

What are your dimensions and what is your expression? Are you able to share a sample?

Re: Pivot table subtotal issue

swathimgumte — Wed, 25 Apr 2018 13:23:05 GMT

Customer and ICATM/USI columns are my dimension and 15 & 16 columns are my expression

refer attachment

Re: Pivot table subtotal issue

sunny_talwar — Wed, 25 Apr 2018 15:11:24 GMT

But what is the expressions? Those are just labels

Re: Pivot table subtotal issue

Qrishna — Wed, 25 Apr 2018 15:33:02 GMT

try this -

if(Dimensionality() = 0 or Dimensionality() = 1 or Dimensionality() = 3 or NoOfRows() > 1,

sum(Amount,'') )

Re: Pivot table subtotal issue

swathimgumte — Thu, 26 Apr 2018 05:56:36 GMT

Hi Sunny,

I have attached my sample file.

Re: Pivot table subtotal issue

sunny_talwar — Thu, 26 Apr 2018 15:33:23 GMT

Can you reduce the size and repost?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Pivot table subtotal issue

swathimgumte — Fri, 27 Apr 2018 06:16:32 GMT

Hi Sunny,

I have attached the reduced data file, check if this is okay with you.

Re: Pivot table subtotal issue

sunny_talwar — Fri, 27 Apr 2018 11:37:56 GMT

Looks much better, thank you... check it now

Re: Pivot table subtotal issue

sunny_talwar — Fri, 27 Apr 2018 12:34:27 GMT

Check this out

I basically looked for expression/s which will have a value in all possible rows and create a variable called vCheck... To be very robust, you might want to use the expression for every single row in vCheck.... I used 15 and 18E because for your sample they covered all the rows between the two expressions....

Sum(TOTAL <DimOverviewCust>

Aggr(If(

$(vICATM_AEFP_m3)

$(vUSI_AEFP_m3)

$(vICATM_AEFP)

$(vUSI_AEFP) <> 0, 1, 0), DimOverviewCust, DimASEGroup)) = 1 and Dimensionality() = 1

Re: Pivot table subtotal issue

swathimgumte — Mon, 30 Apr 2018 05:36:03 GMT

You did it sunny...!

its working perfect as per the requirement.

Thanks a lot...