topic Aging/Sliding-window Aggregation in QlikView

Aging/Sliding-window Aggregation

Thu, 23 Sep 2010 22:18:21 GMT

Hi all,

I can't seem to find any discussion on this, hence this new post.

I have raw data like this:

Day 5 = 30

Day 4 = 40

Day 3 = 25

Day 2 = 20

Day 1 = 45

I need a a report that shows the sum of the Past-N-Days window; e.g. N=2 and 3:

Day 5 | 70 | 95

Day 4 | 65 | 85

Day 3 | 45 | 90

Btw, I have been attempting to solve the problem using SET analysis. The obstacle seems to be that it is impossible to aggregate data beyond the current dimension value. For example, I can't really get the data on Day 4 when my expression is dimensioned by Day 5. Or perhaps my approach in wrong.

I hope someone can help? Thanks.

Aging/Sliding-window Aggregation

pover — Thu, 23 Sep 2010 23:56:27 GMT

You might be better off trying rangesum and above. See the attached example.

Regards.

Aging/Sliding-window Aggregation

johnw — Fri, 24 Sep 2010 00:58:03 GMT

Here's one approach. Two fields, AsOfDay and DaysBack, are explicitly tied to every single day that should be included in that cell. Then you just need a pivot table with those dimensions, and sum(Value) as the expression. Simple!

Except... it won't scale up very well. Basically, you're getting in the neighborhood of the CUBE of the number of days in rows. I already have 2870 rows just for 20 days. If I bump that to 200 days, I get 2,686,700 rows. So it very quickly becomes completely impractical.

Aging/Sliding-window Aggregation

johnw — Fri, 24 Sep 2010 01:01:14 GMT

This looks the same on the surface, except that now our AsOfDay and DaysBack fields are islands. They aren't connected to the main data. Instead, we use an IF statement in the chart to connect them. This CAN become impractical, in that if you have 10,000 days you're looking at, and decide to look at all of them at once, and to up to 10,000 days back, I'm sure that's far more data than the chart could ever process. But even if that much data is available, it's ridiculous to look at that much at once. So in practice, for the number of days and days back that people will likely want to look at, I suspect it would work fine.

Aging/Sliding-window Aggregation

johnw — Fri, 24 Sep 2010 01:26:52 GMT

Thinking further, if you're willing to explicitly limit the number of days back, you can get around the limitation of having too many rows in my first solution. And you can get around even squaring the number of days by using a nice while loop. That might make this a pretty good solution in practice, since it has the advantage of chart simplicity while still scaling up just fine to any number of days.

Aging/Sliding-window Aggregation

Mon, 27 Sep 2010 15:24:51 GMT

Thanks John and Karl, I will try them out today.

Aging/Sliding-window Aggregation

Mon, 27 Sep 2010 17:26:49 GMT

Karl,

Thanks for the hint. It turns out range sum does not include the current row's "today value" (I believe it is beacause we need to turn above() to "below" using -1), so I have to change it to the following and seems to work correctly now. If you can confirm my change is correct that would be great. Thanks:

Original:

Past 2 Day = rangesum(above(column(1),-1,2))

Past 3 Day = rangesum(above(column(1),-1,3))

New:

// reduce above()'s window by one, and add back the current row value instead.

Past 2 Day = column(1)+rangesum(above(column(1),-1,1))

Past 3 Day = column(1)+rangesum(above(column(1),-1,2))

Aging/Sliding-window Aggregation

pover — Mon, 27 Sep 2010 18:33:27 GMT

If your formula works go with it. I don't see anything technically wrong with it.

However, I think you'll get the same result if you use the following formula that might be easier to look at:

rangesum(below(column(1),0,2))

The current row should be included with the value 0 in the second paramente of the functions above() or below(). I think the error was that my days were backwards. I was thinking Day 1 was the most recent day instead of Day 5. And for some odd reason the numbers matched.

Regards.

Aging/Sliding-window Aggregation

Mon, 27 Sep 2010 18:46:13 GMT

Karl, thanks. I just notice the "below" function as I scroll down on the reference manual page.

It turns out there is one more requirement that would extend this thread. It is actually very different from the first requirement but I hope somehow I can leverage above() or similar functions.

What we are counting is actually a usage, e.g. people that ride the bus every day. therefore, what we also like to count in addition to the above, is the UNIQUE bus rider over the day range. I have been trying to use the above() inside a SET expression but no luck so far.

Any advice?

Aging/Sliding-window Aggregation

pover — Tue, 28 Sep 2010 16:33:58 GMT

Bonchef,

I had to think about this for a little bit. There doesn't appear to be a way to use inter-record functions to do a count distinct between various rows. You can only sum the count distinct of each separate day.

Also, with set analysis you can't use the value of the dimension in one line of the table as a reference to sum or count between other lines on the table.

So, the only option I can think of is to use the classic sum(if) with an island table. The only problem with this solution is the time it might take to calculate the table in QlikView.

I've attached an example using sum. You would replace the function with count(distinct rider_id)

Give it a try. I hope it works.

Regards.

Aging/Sliding-window Aggregation

johnw — Tue, 28 Sep 2010 17:46:05 GMT

You can still handle it with the AsOf table that I posted, with no changes. It manages all the connections with data instead of inter-record functions, so you can use all the normal aggregation stuff, such as count(distinct Rider). It does not have the performance issues on large data sets that the count(if()) approach would have. Basically, it takes the performance hit during the load instead, where it is usually more palatable. See attached.

(Edit: Improved performance of the AsOf table load, in case that's an issue. Now using fieldvalue() function instead of loading from a table. It executes significantly more quickly on large data sets, but is a bit more confusing than simply loading distinct.)

Aging/Sliding-window Aggregation

Wed, 29 Sep 2010 14:07:28 GMT

Thanks John, will test that out shortly.

Aging/Sliding-window Aggregation

Thu, 30 Sep 2010 13:35:58 GMT

Just want to share this. When one is doing sliding window in an ad-hoc analysis interactively, this is a very useful trick:

http://qlikviewmaven.blogspot.com/2010/07/stepping-selection-through-each-value.html

Aging/Sliding-window Aggregation

pover — Thu, 30 Sep 2010 17:59:53 GMT

Thanks.