Everything in its Place

 

The Problem

“Is there an easy way to fully expand and fully collapse my pivot chart?” or its cousin,“Can I just press a button to get the pivot chart back to a default?”

These are relatively frequent questions that I never had fully satisfactory answers for.

Before we get into the tip, I used to recommend Right Click à Collapse All or Right Click à Collapse Dimension Columns to collapse the chart and Right Click à Expand All to expand columns (one-by-one).

That solution is not great because first, if you are expanding the chart all the way out, you still have to do it one column at a time.  But the biggest problem presents once you pivot a dimension horizontally.  At that point, you cannot collapse the pivoted column without first un-pivoting the column.

Solution – The Layout Bookmark

The solution to this is actually pretty neat.  It involves a special kind of bookmark – The Layout Bookmark.  Our example will simply involve a collapsed state and a fully expanded and pivoted year column state.

First create a pivot chart and manipulate the columns so that none are pivoted and all are collapsed. 

Then we simply create a document bookmark Bookmarks à Add Bookmarks as follows.



 

 

Notice that we have unchecked “Include Selections in Bookmark”.  We want to be able to invoke this bookmark without affecting the current selections.  The only thing this bookmark will change will be the “layout state” of the application.

Next, expand out all the columns and then pivot the year column horizontally. 

 

 

 

Create another layout bookmark.

 

Now we need to create a couple buttons that will simply invoke each of these layout bookmarks.  But first, we need to know the id’s.  Go to Bookmarks à More…

 

There you can see that the id’s for these bookmarks are BM01 and BM02.  Take note of them.

Finally, create the buttons. 

You can name the button whatever you want and then for the button action, use Bookmark à Apply Bookmark along with the id for the bookmark you with to trigger.

 

Here is the finished product.  Hitting the buttons will expand or collapse the chart to the desired setting.

Notes

There are some complications to be aware of when creating layout bookmarks.

·         The layout state affects the entire sheet.  When you create a layout bookmark, it is taking a snapshot of the entire sheet layout, not just the pivot chart you are concerned with.  This includes minimize/restored settings and cyclic dimensions for all objects within the sheet.

·         The layout state activates the sheet.  This means that if you put this button on a different sheet, it will activate the sheet the bookmark was created on.  This could be good or bad but is important to understand for your use-case.

·         Layout states are hidden in containers.  If your charts are in a container, the layout state for objects within the container will not be saved.

Conclusion

Some of the other things that might be done using layout bookmarks.

·         Default sheet state

·         Set a cyclic dimension to be shown

·         Set a grouped expression to be shown

·         Set restored or minimized objects on a sheet

·         Set which object is shown within a single style container

I would love to hear any other ideas you have for this concept or other feedback.  Happy Qliking.

 

Control Your Date Controls

Do your QlikView applications have date controls?  Almost all of mine do in one way or another.  Most of the time, I have resorted to the traditional Year, Quarter and Month list boxes that we are accustomed to.  Something similar to this:

This is fine for many business users but sometimes users have more exacting needs that cannot be selected with the above controls.  If a user needs to look at the months of November 2012 and Jan 2013 for example, there is no way to make that selection with the above list boxes.  So, we have to add another list box for the Month-Year combinations. 

Then if users have need to look at specific quarters or weeks in the same manner, now we have lots of extra list boxes on the screen that we do not likely have room for.

With the addition of containers, we now have an easy way to provide our users with the best of both worlds.  The key to this idea is that the user likely does not need to utilize both styles of date controls at the same time.  They will need one or the other for any given analysis need.  So hiding one set while the other set is active allows us to reuse the screen area. 

Using nested grid and single-item containers, you can create a very powerful date control set while, preserving the vital screen real-estate for your real data.  You may also incorporate cycle dimensions for a different feel.  Lets look at some examples.  You can find the qvw here.

In the default view, the user sees the traditional date segment view:

If the user selects Range, they will be presented with the ability to select specific year, quarters or months:

 And finally, they can drill down one more step to find specific weeks or dates.

Another option to display the date ranges is to use a list box with a cycle dimension to change the interval type.  This gives us a clean look, allows a larger amount of values to be displayed at once, but limits us to one type of date interval at a time.

There are probably other variations of this idea that may be even more effective and helpful for the user.  Hopefully you can utilize and improve upon this in your own work.

Comments and feedback always welcome.

The iPad and QlikView - A Dynamic Duo

(Warning – This will be light on the technical stuff and heavy on subjectivity for a change.  I will follow up soon with some more technical information on this topic)

There are many famous duos in American popular culture. 

Simon and Garfunkle

Axl and Slash

Batman and Robin

Like all great teams, their value is greater than the sum of their parts.  Great teams are almost inseparable (although Paul Simon had a great solo career).  Great teams complement each other.

So for the consideration of the Academy, I would like to nominate a new great duo:

The iPad and QlikView

After recently picking up the newest iPad, I was pleasantly surprised by the experience.  And I was surprised for a few reasons.  First, QlikView is beautiful on the iPad.  Secondly, it works well.  And lastly, it is FUN.

Of course, every great team has its share of problems.  Axl and Slash hate each other today.  But I will save the minor issues relating the iPad and QlikView for another post.  But I will save any of the minor issues and design workarounds relating to QlikView and the iPad  for another, more in-depth, post.

The Beauty of Mobile Data

I never liked the AJAX experience until recently.  I am so used to the full desktop client that it is hard to swallow all the flaws that become obvious in the AJAX rendering.  With the release of QlikView 11, this has become less of an issue lately. 

But I think the essence of why it translates so well into the iPad is the smaller form factor.  The screen is big enough to provide ample real estate, while small enough to some flaws in pixel-perfect placement.

And the new retina-display allows for a very sharp image.  They “sparkle” off the tablet.  The greater resolution also allows for a perfect rendering when zooming.

Elegance in Performance

It is likely cliché at this point to talk about how the iPad “just works”.  QlikView R&D must have been taking notes, because when you open a document on the iPad, it just works.  Having an interface you can touch is a natural fit for a product like QlikView.  QlikView has taken advantage of many of the inherent gestures and other methods for getting around on iOS.  The interface is intuitive and fast. 

Snapping to fit works very well, generally adjusting the resolution to the smaller of the length or width in relation to the aspect ratio.  Zoom is equally easy and intuitive.  Traditional desktop users will miss the right click and lack of hover, but good design will make up for these issues.

Having Fun with QlikView Again

It is likely a combination of the mobility of the hardware and the excellence in execution from both parties.  I am having fun using QlikView again.

I have found myself spending hours on the couch at night, analyzing my data on the iPad.  Tapping through selections and seeing the changes on various tabs of my documents is actually fun again.  It reminds of when I first started using QlikView.  And the fact is, I can do this analysis on the couch in front of the TV (although the TV is mad because I am ignoring it).  The iPad has reinvigorated my excitement for data analysis and application design.

The Future of this Great Team

The wave is coming.  Organizations are starting to see the value of this new hardware and QlikView has smartly put itself in a perfect position to reap some of those rewards.

I often see commercials for iPads and other tablets where they gush about how useful the tablet is for business.  Invariably there are screenshots of some charts and graphs on the display.  QlikView fits the bill perfectly.  It is the quintessential mobile BI app.  With this new mobility we can discuss scorecards with vendors, industry statistics with clients, and widget production data on the factory floor.  It is definitely coming to a user, client, company, enterprise near you. 

Living QlikView

When people ask me what I do for a living, I often say I am Living QlikView.  And of course, this is primarily because I make my living developing QlikView applications for companies.  But it is also because I love what I do.  QlikView has given me the opportunity to finally give business users what they always wanted:  Effective information that allows them to make better decisions quickly. 

Growing up in the world of distribution, I was constantly challenged to give people information they could use.  And it seemed like the old one-two combo of SQL and Crystal was just never good enough. 

But what if I want to see this by customer?

or

I really want this by week, not month.

or the challenging

I want to be able to toggle back and forth easily through different filters.

or the hardest of all to explain

Why do I always have to call you to get this done?

Then in January 2010 I was introduced to a revolutionary solution called QlikView.  Now I could give users what they always wanted.  I can’t tell people how relieved I am to change my “We can’t do that” to “What else do you want?”  So in the last few years QlikView has completely changed the conversation for my clients.  We used to talk about if we can get a workable report.  Now we talk about “what if” scenarios and what used to be pie-in-the-sky ideas.  We now talk about how to get to better decisions, which is what this should have been all along.  The users have gone from tired-eyed frustration to wide-eyed enthusiasm.  And I have found my place, finally bridging the gap between the technology and the business user.