topic Re: Implement 12 rolling months in in a table ? in QlikView

Implement 12 rolling months in in a table ?

Anonymous — Tue, 15 Apr 2014 20:36:36 GMT

Hello,

Currently the table displays data from jan to current month (see fig below)

Is it possible to display data from current month to previous 12 months

i.e from May 2013 to April 2014 .Want it to be dynamic.(see below)

	May	jun	July	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	April
	52,850	47,806	52,822	51,114	52,818	51,114	52,818	52,818	51,114	52,818	51,114	52,818

Any suggestions?

Re: Implement 12 rolling months in in a table ?

its_anandrjs — Tue, 15 Apr 2014 20:50:00 GMT

Yes yo can make it rolling months refer the below URL for rolling months

Calculating rolling n-period totals, averages or other aggregations

Re: Implement 12 rolling months in in a table ?

Anonymous — Tue, 15 Apr 2014 20:56:26 GMT

Anand,

I did go through the post, I don't know if it would solve my problem.

I was wondering should I use a variable that holds current month ..then display previous months.

What would be the besgt way to implement the solution, using set analysis or in the script editor

Re: Implement 12 rolling months in in a table ?

Anonymous — Tue, 15 Apr 2014 21:06:38 GMT

Is it possible to load only the previous 12 months ?

Re: Implement 12 rolling months in in a table ?

its_anandrjs — Tue, 15 Apr 2014 21:08:39 GMT

In the script level you can do like this

Create two variables which is based on the Monthyear because you want rolling 12 months

vPrevMonthYear = Num(AddMonths(MonthYear,-12)) ;

vCurrMonthYear = Num(MonthYear) ;

Then use in script like

Dimension :- Monthyear

I believe it is a sales value you change according to you

Expression :- =Sum({$<MonthYear = {">$(vPrevMonthYear) <=$(vCurrMonthYear)"}>} Sales)

Regards

Re: Implement 12 rolling months in in a table ?

its_anandrjs — Fri, 18 Apr 2014 11:40:25 GMT

Is rolling back 12 months problem solve if so then mark the thread as Correct or helpful.

Re: Implement 12 rolling months in in a table ?

Anonymous — Fri, 02 May 2014 19:41:22 GMT

Sorry for the late reply,

Do i generate a calendar between the vPrevMonthYear and vCurrMonthYear ?

Re: Implement 12 rolling months in in a table ?

swarup_malli — Tue, 06 May 2014 19:00:01 GMT

Try a creating a flag based on your date field or you could also create a seperate column in the script editor for rolling months

something like this:

(Your_DATE<=date(monthstart(today()),'YYYY-MM-DD') AND Your_DATE >= date(addmonths(monthend(date(today(),'MMM-YYYY')),-12) ,'YYYY-MM-DD'),1,NULL()) as Rolling_flag,
IF(Your_DATE<=date(monthstart(today()),'YYYY-MM-DD') AND Your_DATE>= date(addmonths(monthend(date(today(),'MMM-YYYY')),-12) ,'YYYY-MM-DD'),Your_DATE,NULL())AS ROLLING_DATE,

Hope this helps