topic Re: Month Over Month Line Chart in QlikView

Month Over Month Line Chart

Wed, 27 Feb 2013 23:05:51 GMT

So, a question for the community.

How do you guys do month over month trend analysis? I have a pivot table that works quite well (without set analysis, count(policies)/before(count(policies))) but am having difficulties when trying to portray that visually.

Obviously, the before() function only works in pivot tables, and not in charts.

With set analysis, I can do count($<month='Oct'>}policies)/count($<month='Sep'>}policies) and have that show up as a single bar, but if I put month into the list of dimensions, the chart goes blank, showing no data.

(From what I can gather when I do this QlikView is trying to calculate, for October, all of the policies where (month = October and month = October), divide by all of the policies where (month = September and month = October), and coming up blank.)

Now, I could script each month individually as its own expression, but that limits me to bar charts (line charts won't connect two expressions), and means that I would have to update the object every month, which is unacceptable from a business standpoint.

I confess, I'm rather stuck. Help!

Re: Month Over Month Line Chart

Thu, 28 Feb 2013 01:28:45 GMT

Can you not just use Month as your dimension, the for the expression in your chart use:

Count(Policies)

And if you want it to be cumulative, select Full Accumulation on the Expressions tab.

Re: Month Over Month Line Chart

Thu, 28 Feb 2013 14:05:56 GMT

I can do that, and it gives me a decent graph, but it isn't the expression that I'm looking for.

The expression I wrote above is actually a little simplified, the business metric that I am trying to display is 'monthly retention', which is actually (All October Policies) - (Policies that were new in October) / (All September Policies), which should answer the question, What percentage of policies that were in force in September are still in force in October?

That particular metric has been chosen as one of our Key Performance Indicators, so I need to be able to display it in a visual fashion that is easily accessible by our senior executives.

Good tip about the Full Accumulation thing, though! I can definitely think of other places where I'll use that.

Re: Month Over Month Line Chart

Thu, 28 Feb 2013 15:07:58 GMT

Figured it out!

The correct equation is:( sum(policies) - sum(New business) )/ABOVE(sum(policies).

The clincher was when I changed my chart to a straight table to look at it, and saw how things were organized. If you're ever having trouble with charts, do that!