Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Josh_Good
Employee
Employee

Custom Accumulation

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

5 Replies
nagaiank
Specialist III
Specialist III

A sample application is attached.

Hope this helps.

Josh_Good
Employee
Employee
Author

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.

johnw
Champion III
Champion III

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.

nagaiank
Specialist III
Specialist III

John,

Your 'AsOf' table approach is very elegant.

Thanks

Kris

johnw
Champion III
Champion III

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.

View solution in original post