topic Rolling months per year selected in QlikView

Rolling months per year selected

marcel_olmo — Mon, 20 Dec 2010 19:46:55 GMT

Hey guys, I've been thought the forum the whole looking for this answer, and I also have tried some kind of examples. Specially the posts of John Whiterspoon and Oleg Troianski.

I finally based my script in the file posted in this post :

http://community.qlik.com/forums/t/34450.aspx

Here is what I really want to do :

-I want to calculate a finantial ratio, and I need a straight table that needs the following :

Dimension : Months

Expression : Sales of the last 12 months per each month.

For example :

If I select year 2009, the sales for january would be from feb 2008 to jan 2009, and so on for february, march,...,december.

I'm trying to achieve it with the following script :

Data:
LOAD *
,month(MonthYear) as Month
,year(MonthYear) as Year
;
LOAD
date(addmonths(makedate(2006,12),recno()),'MMM YYYY') as MonthYear
,ceil(rand()*100) as Sales
AUTOGENERATE 48
;

AsOf:
LOAD *,
left(MonthYear,3) as MonthDisplay;
LOAD
MonthYear as AsOfMonth
,date(addmonths(MonthYear,1-iterno()),'MMM YYYY') as MonthYear
,iterno()-1 as MonthsBack
RESIDENT Data
WHILE iterno()<=12
;

Here is my result in tables, if I select Feb 2010.

So, following the philosophy "AsOf" Table I got the desired result, but only if I select an "AsOfYear" previously.

Is there any way to do it dynamically in the straight table once I select one year?

Any help would be appreciated.

Cheers!!

Rolling months per year selected

pover — Mon, 20 Dec 2010 20:50:00 GMT

How about creating an action to select the corresponding AsOfYear if you select a Year or Month and ignoring the Year and Month selected with set analysis when calculating your rolling months?

Regards.

Rolling months per year selected

johnw — Mon, 20 Dec 2010 23:50:38 GMT

Your script doesn't produce an AsOfYear. Simply adding year(MonthYear) as AsOfYear lets you select an AsOfYear and get the right connections to MonthYear. Or you can select only an AsOfMonth and get the right connections without selecting an AsOfYear.

Basically, the point is that the users should be selecting AsOfYear, AsOfMonth and so on, NOT Year, Month and so on. If you let them select the actual year, then the data is limited to that year and you won't get your rolling 12 months. You could change the field names if you don't want them displayed like that. Like using "Month" instead of "AsOfMonth" and "RawMonth" instead of "MonthYear".

But I'm really not clear what problem you're having, and what you want solved.

Rolling months per year selected

marcel_olmo — Mon, 20 Dec 2010 23:59:20 GMT

I've just fix it. I've changed what was missing. Now it worked nice.

All I need to do is to separate the calculations between sheets, because something I'm gonna need "Year" and other times "AsOfYear".

Thanks a lot John for your useful help

Rolling months per year selected

johnw — Tue, 21 Dec 2010 00:13:58 GMT


Marcel Olmo wrote:All I need to do is to separate the calculations between sheets, because something I'm gonna need "Year" and other times "AsOfYear".

One alternative is to add an AccumulationType field to your AsOf table and concatenate some additional rows to let you specify when you mean "just this month" instead of "rolling 12 months".

AsOfMonth, AccumulationType, Month
Dec 2010, Current, Dec 2010
Dec 2010, 12 Month, Dec 2010
Dec 2020, 12 Month, Nov 2010
...
Dec 2010, 12 Month, Jan 2010
Nov 2010, Current, Nov 2010
Nov 2010, 12 Month, Nov 2010
Nov 2010, 12 Month, Oct 2010
...
Nov 2010, 12 Month, Dec 2009
etc.

Now your charts will typically need to use set analysis to lock in to a particular accumulation type. For instance:

sum({<AccumulationType={'Current'}>} Sales) // to get the sales for ONLY that month

But if you go to that trouble, you can now use the AsOfMonth everywhere, not just where you need a 12 month accumulation.

It might be easier to just use AsOfMonth on one tab and Month on another tab, and clear or set one from the other as you change tabs, but that seems a little complicated as well, and might make it difficult to mix the two on a single page, or in a single chart.

Not sure which would be easier or better overall. Just thought I'd present an option.

Rolling months per year selected

marcel_olmo — Tue, 21 Dec 2010 00:40:52 GMT

Thanks again John for your recommendation. I'm gonna try it as well.

Cheers!!

Rolling months per year selected

marcel_olmo — Tue, 21 Dec 2010 01:39:03 GMT

What it could be maybe more interesting is to mix the sum(sales), the accumulative sum(sales) and the rolling of the last 12 month of the sales in the same table.

Actually it was my original idea , and is what I'm trying to mix. It's set analysis hard stuff, but I think it worth it.

Thanks anyway John!

Rolling months per year selected

johnw — Tue, 21 Dec 2010 02:03:41 GMT

Mixing them is simple once the data model is in place. Identify each of those with an AccumulationType. In this case, maybe 'Current','Accumulated','Rolling 12 Months'. Now make a pivot table:

Dimension 1 = AsOfMonth
Dimension 2 = AccumulationType // move to the top
Expression = sum(Sales)

If you need a straight table, and need each accumulation type as a separate column instead of a separate row, it's a little more complicated, but not all that bad:

Dimension = AsOfMonth
Expression 1 = sum({<AccumulationType={'Current'}>} Sales)
Expression 2 = sum({<AccumulationType={'Accumulated'}>} Sales)
Expression 3 = sum({<AccumulationType={'Rolling 12 Months'}>} Sales)

Rolling months per year selected

marcel_olmo — Tue, 21 Dec 2010 02:31:53 GMT

Thanks John Again.

I was struggling about how can I distinguish between them once I get the "AsOF" Table.

As far as I'm concern, the accumulationtype "current" would be when the "monthsback" variable is 0, to count only them.

But I don't get how can I get the others.

My "AsOf" table looks like that :

Rolling months per year selected

johnw — Tue, 21 Dec 2010 02:39:29 GMT

Probably something like this?

Dimension = AsOfMonth
Expression 1 = sum({<MonthsBack={'0'}>} Sales)
Expression 2 = sum(Sales)
Expression 3 = sum({<MonthsBack={">=0 <12"}>} Sales)

Rolling months per year selected

marcel_olmo — Tue, 21 Dec 2010 02:52:29 GMT

Thanks John again!!

Finally I got my 100 % desired result.

Thanks a lot for your useful help.

Now I think I've done with this post. 😉

See you around!!!

Rolling months per year selected

tresB — Tue, 21 Dec 2010 05:58:41 GMT

Hi All,

have a look at this solution: http://community.qlik.com/forums/t/38353.aspx

Regards,

tresesco

Rolling months per year selected

marcel_olmo — Tue, 21 Dec 2010 12:16:14 GMT

Finally, I've attached a demo of my desired result.

I think it could be helpful for other qlikview members.

Cheers!!