topic Re: Custom Accumulation in QlikView

Custom Accumulation

Josh_Good — Tue, 21 Jun 2011 17:08:38 GMT

Hi,

I have monthly data starting in Jan 2008 and I want to do a rolling 12month accumulation. Normally I would just use the accumulation feature onthe expression tab however I do not want to start showing the accumulationuntil I have 12 months of data. i.e. I want the first month on my chartto be 2008-12 but include an accumulation of data from 2008-1.

Any thought on how to do this would be much appreciated.

Thanks,

Josh

Re: Custom Accumulation

nagaiank — Tue, 21 Jun 2011 17:57:05 GMT

A sample application is attached.

Hope this helps.

Re: Custom Accumulation

Josh_Good — Tue, 21 Jun 2011 20:21:18 GMT

Thanks for this. It gets me part of the way however the first 11 months are showing up in the data as nulls. I'd like to get rid of that data so the first row is 200812.

Thanks again.

Re: Custom Accumulation

johnw — Tue, 21 Jun 2011 23:49:39 GMT

One approach is an AsOf table. That lets you select any month independently and still see the 12 months rolling, and also isn't sensitive to the sort order of the chart. See attached.

Re: Custom Accumulation

nagaiank — Wed, 22 Jun 2011 00:06:16 GMT

John,

Your 'AsOf' table approach is very elegant.

Thanks

Kris

Re: Custom Accumulation

johnw — Wed, 22 Jun 2011 00:24:34 GMT

I should probably warn about the practical difficulties applying the AsOf approach to real applications. I told myself I'd start doing that, and then I didn't. Making it user friendly isn't as simple or elegant as the underlying idea.

When you use the AsOf table approach, you're kind of committing yourself. If you leave the regular date fields visible in your application, then things can get confusing. If you select a specific Month, for instance, you will NOT see the rolling 12 months value for that month. Instead, you'll see that month's value for the next 12 months. It's "correct", but almost certainly not what you were after. What you needed to do was select an AsOfMonth instead. So in practice, you have to do a bit more than this example shows. First, you'd want to add a MonthType field or some such to the table, with 'Current' connecting the month to itself, and '12 Months Rolling' connecting it to the previous 12 months. Then you'd want to select and LOCK the 'Current' value. Then never, ever display the Month field for the user. Prevent them from selecting it. Only let them select an AsOfMonth. All normal charts will behave as if it's the Month field, since the locked 'Current' value connects it to the equivalent Month. Then for other charts, you'd need to use set analysis to override 'Current', so the chart I showed would instead be count({<MonthType={'12 Months Rolling'}>} ID). And at that point, since as far as the user knows your AsOfMonth IS the Month, you should probably rename fields to reflect what the user is actually seeing.

I don't have a full example of the full blown thing at this point, though I really need to build one or two or five. I have a partial example, though, where I added some bits of it to someone else's data. See attached. It might at least be helpful.