topic Re: Rolling 60 for each month in QlikView

Rolling 60 for each month

Mon, 05 Dec 2011 22:26:29 GMT

Hi Eveyone,

I'm looking to get a rolling 60 day sum for each month. Not just the last 60 days. See example. How would I implement this?

I currently have a line graph with the last 60 days in the expression. I want to the last 60 days for each month. Does that make sense?

Alex

Rolling 60 for each month

Mon, 12 Dec 2011 22:42:28 GMT

anyone?

Re: Rolling 60 for each month

johnw — Tue, 13 Dec 2011 02:05:31 GMT

This may be similar to what you're asking for. The three charts show three different options. In this case, they show a rolling three months, but it's hopefully fairly clear (with some study, I'm sure) what to do to convert any of them to rolling 60 days.

Rolling 60 for each month

Tue, 13 Dec 2011 16:40:19 GMT

Awesome John. I used the last of the three charts in my application and it worked great. It should be ease to replicate for 60 days. 90 days was actually a requirement also. I really appreciate it!

Alex

Re: Rolling 60 for each month

Thu, 15 Dec 2011 18:11:22 GMT

Hey John, I more thing. I'm trying to add another dimension to the graph, but it only shows the 1 typ of my dimension at a time. But if I select the dimension in a list box it renders correctly, but not all of them are shown on the graph. Any ideas what I need to change?

Re: Rolling 60 for each month

johnw — Thu, 15 Dec 2011 20:18:20 GMT

You can only have two dimensions, I believe - your X axis dimension, and the dimension that controls how many lines are being displayed. If you MUST have a line for each combination of year and some other dimension, you could concatenate the two together as a calculated dimension. I think for most data sets that would simply produce a mess, though.

Perhaps you should be using a year+month as the X axis, and use your other dimension for the lines in the chart.

I'm not entirely sure I've understood what you're trying to show, though, or what your data looks like.

Re: Rolling 60 for each month

Thu, 15 Dec 2011 20:23:08 GMT

I only have two dimensions. AsOfMonth, and Region. I don't understand why its not showing a line for Region. I attempted to use YearMonth as the X Dimension, but it still did not work. It just doesn't make sense to me, because I can select each Region and they render as single line on the graph.

Re: Rolling 60 for each month

johnw — Thu, 15 Dec 2011 20:35:30 GMT

Works fine for me and nothing is jumping out at me as a possible explanation. Maybe if you have an expression with an aggr() somewhere, you could have forgotten to add Region to it, but that's grasping at straws. I don't know your data model or chart expressions, so I can't actually debug.

Re: Rolling 60 for each month

Thu, 15 Dec 2011 21:16:41 GMT

Not grasping at straws at all. That was the key. So if I have an with an aggregation, I have to add the dimension in the expression? I'm not very well-versed with the aggregation functions. Thanks again John.

Re: Rolling 60 for each month

johnw — Thu, 15 Dec 2011 22:36:35 GMT

Let's say we start with a chart that looks something like this:

Dimension = Month Year
Expression = avg(aggr(sum(Sales),Customer))

So for each month, we want to know the average of customer sales. Now you decide you want to know the average of customer sales by Region. If you just add the Region dimension with no further changes...

Dimension 1 = Month Year
Dimension 2 = Region
Expression = avg(aggr(sum(Sales),Customer))

...it probably won't work right, because the aggr() is, I believe, explicitly telling QlikView to ignore the Region field. So I'd expect all of the lines to overlap, and only the top line would show. I think you would need this instead:

Dimension 1 = Month Year
Dimension 2 = Region
Expression = avg(aggr(sum(Sales),Region,Customer))

I'm not entirely certain, though.

Re: Rolling 60 for each month

Wed, 21 Dec 2011 19:45:49 GMT

Hey John,

I'm dealing with some slowness when the chart is rendering. I have 34 Million records that I'm using at the moment. Do you have any other advise for me to try and increase the speed of the calculation?

=(sum(aggr(sum({$<AsOfYear={2011},RollingTwoMonthsFlag={1},TransactionType={1}>}Cnt),AsOfMonth,Region))-sum(aggr(sum({$<AsOfYear={2010},RollingTwoMonthsFlag={1},TransactionType={1}>}Cnt),AsOfMonth,Region)) )/sum(aggr(sum({$<AsOfYear={2010},RollingTwoMonthsFlag={1},TransactionType={1}>}Cnt),AsOfMonth,Region))

Rolling 60 for each month

johnw — Wed, 21 Dec 2011 20:00:30 GMT

First, recognize that (A-B)/B = A/B - 1. That might save some time, assuming QlikView doesn't realize they're the same calculation and grab the result out of some cache. It'll simplify the expression in any case.

I suspect you can eliminate the sum(aggr(sum())) structure and just use sum(). It seems like they would logically produce the same result. You're throwing values into buckets, totalling up each bucket, and then totalling the totals. That should be the same as just totalling the values. In the case of avg(aggr(sum())), you'd get different results, which is probably why I mentioned that as an example of aggregation.

Are your flags 1/0 or 1/null? You might find 1/null to be a little faster.

In some versions of QlikView, count() is faster than sum(), and in others the opposite is true. It might be worth trying as a count.

There may be a way to arrange the data better to match your specific case. For instance, if TransactionType isn't a flag, but has values 1-6, and is on the same table as Cnt, you might define a "Transaction Type 1 Cnt" field that has a value of 1 or null. It might be faster to sum or count that field than to include TransactionType in the set analysis in that case. I don't know (and don't have time to learn) your full data model, though, so I can't really say how you might change it.

Re: Rolling 60 for each month

Wed, 21 Dec 2011 20:48:35 GMT

I changed the calc to A/B-1 and that didn't seem to do a whole lot, but when I removed the aggr() function. It significantly increased the speed.

Re: Rolling 60 for each month

Tue, 10 Jan 2012 22:23:56 GMT

Hey John,

So my requirements how changed a little bit. I now need to compare it by date, not just month. They customer would like to go back 28, 56, and 74 for their 30,60,90 day views.

Right now, I'm at

if(MonthYear<=AsOfMonthYear and AsOfDate>(As)fDate-74),1) as Rolling ThreeMonthsFlag

But thats not getting me my expected results. Any pointers for me would be great.