Sparkline Some Confusion

Just remember what ol' FortuneCookie Mike does when the earth quakes, and the poison arrows fall from the sky, and the pillars of Heaven shake. Yeah, FortuneCookie Mike just looks that big ol' storm right square in the eye and he says, "Give me your best shot, pal. I can take it."

Except when you present the wrong picture causing your users to make bad decisions. I can't take that.

A good picture is a mini chart. I like them. They give boring tables a little spark. (Pardon the pun)

Remember that a sparkline (line chart) shows a trend over time. It is extremely important to sort your dimension correctly. Unfortunately, QlikView doesn't give you control over the sort in a mini chart. When this dawned on me, I threw it out to the Qlik Community. I though that it might be sorting by load order, but I was wrong.

One of the experts did a little experimenting. (Hat tip to Gysbert) The bottom line is that a mini chart sorts numerically if the dimension is numeric and alphabetically if the dimension is a string.

If you don't understand what you're creating, it might well be wrong.

To demonstrate, we have a very simple document with the following load:

     T1:
     load * inline [
          YearMonth, Product, Sales, MonthYear,
          201203,A,10, Mar 2012,
          201209,A,20, Sep 2012,
          201204,A,30, Apr 2012,
          201212,A,40, Dec 2012,
          201207,A,50, July 2012,
          201210,A,60, Oct 2012,
          201202,A,70, Feb 2012,
          201206,A,80, Jun 2012,
          201201,A,90, Jan 2012,
          201208,A,100, Aug 2012,
          201211,A,110, Nov 2012,
          201205,A,120, May 2012
     ];

YearMonth

YearMonth is a numeric field. April, 2012 is formatted '201204'. The mini chart wills sort it in ascending order. Compare a line chart with the Sparkline:

Everything looks just like you would expect. You show the user the trend by month over time. But the format of Year Month is ugly. So you try MonthYear.

MonthYear

MonthYear is a string field. April, 2012 is formatted 'Apr 2012', which is a much better presentation for a chart. To make the line chart sort correctly, we would use an expression on the sort tab:

Unfortunately, you can do that in a mini chart. You wind up with the wrong shape:

The Solution

When some wild-eyed, eight-foot-tall maniac grabs your neck, taps the back of your favorite head up against the barroom wall, and he looks you crooked in the eye and he asks you if ya paid your dues, you just stare that big sucker right back in the eye, and you remember what ol' FortuneCookie Mike always says at a time like that: "Have ya paid your dues, Mike?" "Yessir, the check is in the mail."

Or, in this case, the solution is easy. Use YearMonth in your mini chart and MonthYear (with the correct sort order) as your chart dimension.