topic Re: To add a single total row in Pivot table for conditional dimensions. in QlikView

To add a single total row in Pivot table for conditional dimensions.

Wed, 23 Nov 2016 07:23:34 GMT

Hi All,

I am facing below problem with pivot table.

Situation: I have a pivot table with multiple dimensions which are conditional. i.e. a particular dimension in pivot table will be visible on selections in another list box. At a time a single dimension will be available always (Geography by default). See attached screenshot.

Requirement: I want to add a single total row at top. Irrespective of number of selections of dimensions; there should be just 1 row.

Challenge: If i enable the show partial for all dimensions then corresponding to all of them multiple total rows will appear if all dimensions are selected.

Any kind of help would be appreciated.

Re: To add a single total row in Pivot table for conditional dimensions.

prat1507 — Wed, 23 Nov 2016 07:41:04 GMT

Hi Shilpa

In Presentation tab, enable show partial sums for the first dimension, which I see is 'Region' in your case. For the rest of the dimensions, keep it disabled. Now select Subtotals in the top in the Subtotals option. I hope you'll get the desired result.

Regards
Pratyush

Re: To add a single total row in Pivot table for conditional dimensions.

Wed, 23 Nov 2016 08:08:05 GMT

Hi Pratyush,

Thanks for your response.

But the problem is that Region will not always be the first dimension. If i enable product and disable Geography, product will become first dimension.

And if i add show partial sums to product as well, then on selection of both there will be two rows of total.

Similar case applies to all other dimensions.

Regards

Shilpa

Re: To add a single total row in Pivot table for conditional dimensions.

stabben23 — Wed, 23 Nov 2016 09:28:22 GMT

Hi, have a look into this Application and see if this helps.

Re: To add a single total row in Pivot table for conditional dimensions.

Wed, 23 Nov 2016 09:33:58 GMT

@Staffan : I can't see any attachment !

Re: To add a single total row in Pivot table for conditional dimensions.

stabben23 — Wed, 23 Nov 2016 09:46:58 GMT

I'll send a better updated one:

Re: To add a single total row in Pivot table for conditional dimensions.

stabben23 — Wed, 23 Nov 2016 09:47:48 GMT

Do you use Ad hoc Dimension picked from a listbox?

Re: To add a single total row in Pivot table for conditional dimensions.

Wed, 23 Nov 2016 09:54:08 GMT

Not ad hoc.

What we actually do is ... we make selection in list boxes , some variable get set. The dimensions in pivot table are conditional on those selected variable.

Re: To add a single total row in Pivot table for conditional dimensions.

stabben23 — Wed, 23 Nov 2016 10:03:06 GMT

then look into my second atachment if you see it!

Re: To add a single total row in Pivot table for conditional dimensions.

Wed, 23 Nov 2016 10:38:11 GMT

Hi Staffan,

Thanks a lot ....the file was of great help...got a different perspective to solve this problem...working on it.

There is another perspective i am working on ......It would be great if you can provide your inputs

1. Added a blank dimension (unconditional) and enabled Partial Sums for it only. (See attached screenshot).

Problem is ..i have to hide this column...If i hide it using macro ...The "Total" will also get hide....Is there any way in QV by which we can shift position of this "total" label?

Re: To add a single total row in Pivot table for conditional dimensions.

prat1507 — Wed, 23 Nov 2016 11:03:30 GMT

Umm, this is a workaround I know it may not work that well or look good. But, you can add a calculated dimension.say 1, promote it to the top, disable partial sums in the presentation tab for all the dimensions except for this one. Select Subtotals on the top. And done!! PFA the .qvw. I'd used Staffan's file. Thank you and sorry Staffan!!

Re: To add a single total row in Pivot table for conditional dimensions.

stabben23 — Wed, 23 Nov 2016 11:07:50 GMT

No problems prat1507‌

Re: To add a single total row in Pivot table for conditional dimensions.

Wed, 23 Nov 2016 18:39:25 GMT

Thanks Pratyush.

Yeah..i did same.....But ...the problem is if i hide dummy column using Macro...is there a way to push the "total" label which appears on total line; to next column ?..Basically i don't want to hide the "Total" Label.

Any suggestions over this ?

Re: To add a single total row in Pivot table for conditional dimensions.

prat1507 — Wed, 07 Dec 2016 10:28:41 GMT

Hi Shilpa

Sorry to keep you waiting, I was away for a while. What you can do is hide the totals there on the chart as there's no way to move it to the next column, however you can add a text object as Totals keeping is position fixed and making the background transparent. This is not the best method but the end result is achieved. PFA.

Regards

Pratyush