topic Re: Issues Creating Dynamic Pivots in QlikView

Issues Creating Dynamic Pivots

Mon, 04 Feb 2013 14:42:38 GMT

Hiya Guys,

I'm attempting to create a self-service Dashboard for our Users so they can choose their own criteria (ie: Date Ranges, Dimensions, Facts) and then produce a Straight/Pivot Chart giving them the data they require (which can then be exported to Excel, for example).

I've gotten as far as being able to dynamically show/hide Dimensions/Facts via List Boxes - and all was looking great. However, I am having trouble finding a solution which will allow the Users to Group and Total the data how they please.

For example, adding Partial Sums will effectively total the Dimension directly to its 'left' (unless I am misunderstanding?) meaning that, if the User drags and drops columns into a different order, then any Label names will not make sense (and therefore I am forced to use either "Total" or [blank]).

Also, as there are a fair number of Dimensions, I don't always want ALL Dimensions to have a Partial Sum - and I can't see any way of making this option conditional?

Finally, some Dimensions are the same granularity (ie: EmployeeCode and Employee is a one-to-one relationship) and so I do not want to show a Total on both of these Dimensions if both are visible (however if either one is selected then I would like the User to have the option).

Is there a best practice way of achieving this? I assumed this was one of the fundamental uses for QlikView - but I have found a solution difficult to come-by.

I may not have explained my points very well - so if you require any further information please let me know.

Cheers,

Steve.

Re: Issues Creating Dynamic Pivots

Tue, 26 Feb 2013 11:40:03 GMT

*BUMP* 🙂

So far I have partially-solved the issue by removing all Totals if a Straight Table is selected, and adding Totals only on the "Code" fields if a Pivot Table is selected (therefore allowing the User the choice of being able to directly export into Excel via the Straight Table, or view the data on-screen with Totals via the "Code" fields on a Pivot Table).

The Users are happy with this outcome - although, for me, I would much prefer a more robust solution.

If no one else has any further ideas I will mark this as the "Correct Answer" - and I will also look at suggesting this feature for future releases.

Cheers,

Steve.

Re: Issues Creating Dynamic Pivots

bdunphy — Tue, 02 Apr 2013 22:02:56 GMT

Hi Steve,

I had this issue too - I wanted to create an ad-hoc sheet where users can create their own report. Only problem was lack of option to conditionally show partial sums....

A workaround I came up with is to add a blank calculated dimension - defined as ' ' and give it a label of '*Drag to create subtotal' . Set this to partial sum = 'YES'

Set up a list box for your subtotal field - 'Show Subtotal' and make your blank expressions conditional on this being selected.

the user can then select to show a subtotal and their subtotal blank field will be displayed. it can then be dragged to create a different total level..

Brian

Re: Issues Creating Dynamic Pivots

Wed, 03 Apr 2013 14:55:26 GMT

Hiya bdunphy,

Thanks for your input - I'd be interested to see a working example of this!

Cheers,

Steve.

Re: Issues Creating Dynamic Pivots

bdunphy — Wed, 03 Apr 2013 15:22:49 GMT

see attached file.

This is the whats new in v11 file reduced slightly for size and then with the subotal otion added to the report on the report tab.

It needs to be changed to a pivot table (you could turn pivoting off if necessary) and then you set this 'fake' dimension to be partial sum=YES

I use a button to toggle whether or not a subtotal is necessary

Brian

Re: Issues Creating Dynamic Pivots

Wed, 03 Apr 2013 15:49:57 GMT

Thanks bdunphy,

I like this solution!

I'd have to play with this some more to tweak it - and it obviously limits you to only one Total... but it's definitely another tool in my arsenal! 🙂

Cheers,

Steve.

Re: Issues Creating Dynamic Pivots

bdunphy — Thu, 04 Apr 2013 08:38:39 GMT

Had a play around with this and you can easily add a couple of buttons to add additional subtotal levels. then set the second subtotal calc dimension to a condition of 'vShowSubtotal>1' See attached for example

Re: Issues Creating Dynamic Pivots

Wed, 17 Apr 2013 16:52:02 GMT

Thanks for all your help bdunphy,

In relation to the initial problem I have submitted a development idea:

If you think it's worth noting please vote! 🙂

Cheers,

Steve.

Re: Issues Creating Dynamic Pivots

Tue, 04 Aug 2015 09:38:16 GMT

Maybe I'm thinking to simple: I have just added a dimension twice: once with Partial Sum Activated and Once Without (to add a field the second time just select "add calculated dimension" and type in the field name WITHOUT brackets and WITHOUT the = sign).

Then I added a button to the set a variable on click: showSums = 1-showSums (<= this generates a toggle in the value)

and then used a "enable conditional" on the 2 versions of the same field:

once showSums = 1 and once showSums = 2

Regards

Petr