Month Over Month Line Chart

[#1]

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!

[#2]

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.

[#3]

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.

[#4 · Accepted solution]

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!