topic Re: Issue with pivot chart subtotal in QlikView

Issue with pivot chart subtotal

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

Hi all,

I'am facing an issue with subtotal. I have a pivot chart with 13 dimensions and 2 expressions (Z, Y). I'd like display in sub total for dimensions J and L a sum or avg. (first snaphot below)

I manage to display this only if i tick the "Show partial sum" for J and L. But the formatting pivot has changed.(see second snapshot below) .

So i want only subtotal for dimensions A and B, but display formula for dimensions J and L. Is there a way to achive this without having to put J and L in expressions.

Regards

Re: Issue with pivot chart subtotal

MindaugasBacius — Fri, 12 May 2017 11:30:37 GMT

Add those field into expression as

Sum(Field1)

Re: Issue with pivot chart subtotal

sergio0592 — Fri, 12 May 2017 19:40:37 GMT

Yes, there is no way except put these fields as expressions as proposed by Mindaugas.

But, i have a new issue. I can't understand because for me i thought that very manageable. I want as subtotal the number of months in the chart below (and in the the attached file). I have tried with count, RowNo(),... and as a result i have 7 so, it's should be the result for : Count(TOTAL Month) and not for Count(Month) which should return 4 for Russia and 3 for UK .

Re: Issue with pivot chart subtotal

vishsaggi — Fri, 12 May 2017 20:21:38 GMT

May be:

Count(Distinct Month)

Re: Issue with pivot chart subtotal

sergio0592 — Fri, 12 May 2017 20:26:50 GMT

Don't works, returns 4 .

Re: Issue with pivot chart subtotal

vishsaggi — Fri, 12 May 2017 21:10:33 GMT

Try like

= Sum(Aggr(Count(Distinct Month), Country))

Re: Issue with pivot chart subtotal

sergio0592 — Fri, 12 May 2017 21:30:58 GMT

Don't works, subtotal stays at 7 .

Re: Issue with pivot chart subtotal

vishsaggi — Fri, 12 May 2017 21:36:55 GMT

Can you share a sample app with the data if possible?

Re: Issue with pivot chart subtotal

sergio0592 — Fri, 12 May 2017 21:40:19 GMT

Qvw sample is already in attached file in one of my previous post.

Re: Issue with pivot chart subtotal

Digvijay_Singh — Fri, 12 May 2017 21:40:48 GMT

May be -

Count(Total<Country> Distinct Month)

Re: Issue with pivot chart subtotal

sergio0592 — Fri, 12 May 2017 21:45:01 GMT

Don't works, return 4 like with just =Count(Distinct Month)

Re: Issue with pivot chart subtotal

sergio0592 — Fri, 12 May 2017 22:15:50 GMT

I manage to get 4 and 3 in calculated dimension with =aggr(NoOfRows(),Country,Month). But can't get it in subtotal...

Please stalwar1‌ help us !!

Re: Issue with pivot chart subtotal

sunny_talwar — Fri, 12 May 2017 22:23:30 GMT

Subtotal can only show a single value (mostly used to show the text 'Total'), but you might be able to play around using the Pick(Dim(...)) technique or play around with this Partial Sums in straight table | Custom Labels in Pivot SubTotals

Re: Issue with pivot chart subtotal

sergio0592 — Sat, 13 May 2017 12:20:28 GMT

Thanks for your reply. Can't understand how pick function can be helpful but in order to get a single value in subtotal, i use min and max with =min(aggr(NoOfRows(),Country,Month))

=max(aggr(NoOfRows(),Country,Month))

I have value for Russia in one chart (4) and value for UK (3) in an other but can't get in the same chart.

Re: Issue with pivot chart subtotal

sunny_talwar — Sat, 13 May 2017 12:31:38 GMT

This is what I implied

Re: Issue with pivot chart subtotal

vishsaggi — Sat, 13 May 2017 14:47:35 GMT

Pick with ISLAND Dim table load helped me in few situations like a charm. Thanks very much Sunny for this trick.

Re: Issue with pivot chart subtotal

sergio0592 — Mon, 15 May 2017 08:03:21 GMT

Thanks a lot for your help and for this useful trick!!