Monthly Rolling

[#1]

Hey Guys,

I am trying to calc revenue year over year. The trick is they want it rolling every 3 months.

I was able to do the calc and use accumulation with 3 steps back. The problem is the first few months need to use the a few last months of last year. Right now for example Feb is averaging Jan and Feb but I need it to calc Dec from the previous year as well.

Any ideas?

[#2]

Are you using AddMonths to calculate the months to include? If I understood your problem, you aren't using date arithmetic. If there is another problem, please add a sample code.

[johnw]

Well. That turned out to be much more difficult than I expected.

In the attached solution, I use a date island in combination with this rather complicated expression:

avg(aggr(if(sum(if(MonthYear<=ChartMonthYear and MonthYear>=addmonths(ChartMonthYear,-2),Revenue))
,sum(if(MonthYear<=ChartMonthYear and MonthYear>=addmonths(ChartMonthYear,-2),Revenue))),ChartMonthYear,MonthYear))

It does what I think you want, but it will have the usual drawbacks of date islands, mostly performance. And I'm convinced there's a better solution than mine. Still, some solution is better than no solution, so here you go.

[Oleg_Troyansky · Accepted solution]

Kristen,

the best way of addressing rolling month averages is creating two sets of dates and linking them in a separate table, specifying for each date its relationship to the other date. I'm sure it's very confusing, so I'll try to explain:

1. Date # 1 is your Transaction Date.

2. Date # 2 is your "Display Date" (or, sometimes we call it "As of" date.

Create a table with all possible combinations of the two dates. You can do it by joining the two fields with no common keys - QlikView will conveniently create a Cartesian table of the two.

Now, calculate flags like "3_Rolling_Months_Flag", "3_Rolling_Month_Prior_Year_Flag", etc, using IF conditions and checking how one date relates to another. For example:

if( AddMonths(Transaction Date , 3) <= DisplayDate, 1, null()) as 3_Rolling_Months_Flag

(Using IF statements in the load script is not as bad as using them in the chart expressions)

Now, with the flags in your hands, you can use them in a couple of ways - either multiplying your amounts by the flag, or checking the flag in Set Analysis. The later is faster, if you are comfortable with the syntax. Examples:

sum(Revenue*[3_Months_Flag]) - will only summarize those transactions associated with the Transaction Dates that have Flag=1

The same result (a bit faster) can be achieved with the following:

sum( {< [3_Months_Flag] = {1}>} Revenue)

Your Dimension (and Selection field in List Boxes) should be the "Display Date", not the Transaction Date.

cheers,

[johnw]

Ah, good. There WAS a better solution than mine. Yeah, a data solution like this is probably the best way. I've added that approach to my example. The expression is complicated by the need to get a monthly average for the three months, but it is still MUCH simpler than what I had (and will execute more quickly, and selections will behave properly, and so on).

avg(aggr(sum({<RollingThreeMonthsFlag={1}>} Revenue),AsOfMonthYear,MonthYear))

If your ONLY flag ends up being for the rolling three months, you don't need a flag or set analysis at all. Just drop all the rows that don't match the rolling three months. I've added that approach too. The expression ends up like this, which I think is as simple as it's going to get while still doing a monthly average.

avg(aggr(sum(Revenue),RollingThreeMonthsAsOfMonthYear,MonthYear))

[#6]

Thanks for all your help on this one. We were able to get it working.

[#7]

Great solution!

Thanks for sharing!

🙂 Fredrik