Pivot Table Functionality.. or lack thereof?

[#1]

I am currently trying to empower my users to ask their own questions of data in Qlikview via pivot tables.  Unfortunately I believe Qlikview may be found lacking in my objectives.  Pivot tables are great for viewing data in different views (as decided by the user) as the user can move the columns around, so instead of seeing a pivot table drilled down by dimensions A, B, and C, the user could view the dimensions as C, B, and A, providing a different analysis.

However, when you want to empower a user to use more dimensions, say 15, then the pivot table becomes unwieldy as they may have to have all 15 columns expanded to start with so they can drag&drop the columns where they see fit before collapsing the columns (i.e. dimensions) to the right that they do not want to see.  Also, adding the dimensions via 'properties' is not an option due to the large number of characteristics in the qvw due to its complexity and the users desire not to have to scroll through 100+ dimensions to find the few that they want to interrogate (the 100+ dimensions come from a complex model that covers numerous data sources and aspects).

What I really want is simply the functionality that is available within excel.  I want a pivot table with an initial simple view, for example A, B, and C with the ability for a user to remove a dimension and drop in two or three dimensions from a selection of say 10, thus circumventing the need to trawl through 100+.

Am I missing a trick or is there no easy way for a user to drag and drop dimensions into and out of a pivot table from a subset of relevant dimensions?

Many thanks for any helps that can be provided.

Steve

[johnw]

You're not missing any trick that I'm aware of.  There's no easy way to do it.  Fortunately, at least there's a difficult way to do it using macro code.  See the attached example.  It allows the user to select both which dimensions and which expressions they want to see.  The code would be a little simpler if you don't need them to be able to select expressions.  To have an initial simple view, just select the fields you want to be in the initial simple view and set it up like you want to see it before saving.  That's then their default, and they can modify from there.  If you only want to give them access to 10 of the data model's 100 dimensions, just put 10 in the list box.

[chriscammers]

I think you are being a little bit to simple in your approach.

Providing 15 dimensions in a single pivot table is a traditional OLAP/BI approach. You should explore some of the functionality with drill down hierarchies, Cyclic Hierarchies.

List boxes also provide a very underated piece of functionality where a very simple chart can represent the sum of whatever selections your users want to make. Also if you try out the search boxes you will find that users can almost instantly find the business term they are looking for by just typing it into the box and choosing the places where it occurs in the List.

Rather than attemping to only reproduce the functionality they could get in Excel try using some things that may not be exactly like what your users are used to but may with a little guidance amaze them.

[johnw]

chriscammers wrote:

Rather than attemping to only reproduce the functionality they could get in Excel try using some things that may not be exactly like what your users are used to but may with a little guidance amaze them.

A good point.  While I've implemented dynamic reporting in four different production applications, I hope and suspect that it is only very rarely used.  Letting the users build their own charts, while powerful, should not be a substitute for learning what sort of information the users really need, and providing it to them in a clear and concise way.  At most, dynamic charts in the style we're discussing should be an additional feature provided to the most sophisticated users.  But we should always strive to make this feature as unnecessary as possible.  My opinion, anyway.

[#5]

Thank you both (and apologises for the late reply). 

John - The macro looks really useful, I hope to get some time to try to implement this in my model next week. I'll let you know how I get on.

Chris - I understand what you say here, but I think this method provides more flexibility i.e. in letting the user ask the question and the tool providing the answer, rather than expecting the user to ask the 'right' question.  Drill down hierarchies have to be preconfigured and therefore only have one set path while cyclic groups presents the situation of one option but not all or a subset.  This has however got me thinking of another work around which is several identical cyclical groups which the user then chooses how they want the breakdown, i.e. Country, Customer, Product or Product, Country, Customer, etc. 

Any thoughts on the above is whole-heartedly welcome.

Many thanks

Steve

[chriscammers]

I think the thing I did not communicate very effectively is that you were saying that the only way to meet your users requirements was to present a table with a huge pile of data in it and then letting your users mess with the table. I agree that sometimes you cannot anticipate what combinations of columns the users will want to see in a particular chart but using the knowledge of the business that you have gained from interviewing your users should go a long way into figuring out the most common ways that the business thinks about the data.

Providing many charts and tables (minimized or as part of a container) with different twists and flavors will go a long way to providing 90% of what your users will need. Then allowing some of your users to clone objects and manipulate the dimensions will complete ther other 10%.

The key thing to watch for is are your users constantly dumping the data out to excel and messing with it there. If they are then you are not providing the value they require. Either you don't have the views they need or there is external data that needs to be added to your QVW.

There is a common Business Intelligence paridigm where as soon as you finish writing a report then there will be more to do because the answer to one question prompts another. 

[#7 · Accepted solution]

I managed to answer my own question.  Version 11 provides this facility automatically apparently, but as we are still on Version 10, the way to do this is to load an inline table with the names of the Characteristics required, and then populate a straight/pivot table with the following calculated dimension;

=$(=subfield(GetFieldSelections([Report_Dimension_Name]),',',1))

Depending on how many columns you let users utilise, you can then add a 2nd/3rd/etc. dimension by adding further calculated dimensions as per the below (note the change in number only);

=$(=subfield(GetFieldSelections([Report_Dimension_Name]),',',2))

=$(=subfield(GetFieldSelections([Report_Dimension_Name]),',',3))

This also has the benefit of hiding any non-utilised dimension (i.e. only two dimensions will show should you select only two characteristics, even if you have 3+calculated dimensions).

I hope this helps someone.