# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
New Contributor III

## Dynamic Rolling Period 12 months

Hello Guys,

I would like to calculate the revenue(columnname Omzet) based on a rolling periode of 12 months vs. the same period a year ago. So I created the following variables:

vRollingYearStart 1-8-2016

vRollingYearEnd 31-7-2017

vRolling2ndYearStart 1-8-2015
vRolling2ndYearEnd 31-7-2016

I've tried to calculate several formulas, bur for some reason I'm not get an correct result.

Could someone help me with this one?

Would help me a lot!

Tags (3)
6 Replies
New Contributor III

## Re: Dynamic Rolling Period 12 months

In the script, I set this variables:

 LET vYearBefore =  Year(Today())-1; // year prior current LET vMonthBefore = Month(Today())-1;//month prior current

Using Set Analysis:

 sum(     {<         [Year]={\$(=vYearBefore)}         ,[Month]={">=1<=\$(=\$(vMonthBefore))"}     >} [Value]     )

Making dates (I always use the first day of the month, if you need another day, you can set a variable in the script😞

 LET vDate = makedate(\$(vYearBefore), \$(vMonthBefore))

You can replace variables with functions, I use variables because y need them for another non related procedures

EDIT:

You can set flags in the script too:

 LOAD      *      ,      If( monthstart([DateField]) > monthstart(addmonths(Today(),-12)) and          monthstart([DateField]) <= monthstart(Today())         ,1         ,0     ) as _R12 Resident table;
New Contributor III

## Re: Dynamic Rolling Period 12 months

Hello Victor,

I think youre not getting me right. I've tried your solution but it give me the revenue for the period 1-1-2016 till 31-7-2016.

But im looking for a calculation to dynamicly calculate a rolling period for:

last month till last 12 months

vs.

last 13 months till last 24 months.

That's why i created the date calculation by variables.

Do you or anyone else have an idea?

Partner

## Re: Dynamic Rolling Period 12 months

Hi,

I've made quite a few of these, and my go to solution is always to index every month in the data model during script; eg.

AutoNumber(Year, Month) as MonthIndex

Which would make the model look something like:

Year     Month     MonthIndex

2016     Jan          1

2016     Feb          2

...

2017     Jan          13

2017     Feb          14

etc.

And then I just use this in the set analysis:

Sum({<Year =, Month =, MonthIndex = {">\$(=Max(MonthIndex)-12)<=\$(=Max(MonthIndex)"}>}Revenue)

This way you can easily change from rolling 12 to rolling 3, 6 or whatever by setting the number of months in a variable.

New Contributor III

## Re: Dynamic Rolling Period 12 months

• If today is 11/08/2017
• Period 1:
• first date: 11/08/2016
• last date: 11/08/2017

• Period 2:
• first date: 11/08/2015
• last date. 10/08/2016

If this is correct, in the script:

 LOAD      * ,      If(  ([FieldDate]) > (addmonths(today(),-12)) and   ([FieldDate]) <= (Today())    ,1      ,0    ) as _Period_1    ,If(    ([FieldDate]) > (addmonths(today(),-25)) and  ([FieldDate]) <= (addmonths(today(),-13))   ,1    ,0    ) as _Period_2 Resident TABLE;

With Set Analysis:

 sum(  {<   [_Period_1]= {"1"}     >}  Value)
New Contributor III

## Re: Dynamic Rolling Period 12 months

Hello Niels,

Thanks for your reply. Unfortunately I don't have a Monthnumber or in your example MonthIndex in my calendar.

Could you help me how to set this one in the script?

Further I would like to compare the rolling 12 months with the rolling 13 till 24 months. Is this also possible with your example above?

Partner

## Re: Dynamic Rolling Period 12 months

Hi,

I provided the MonthIndex script in my previous reply;

AutoNumber(Year, Month) as MonthIndex

Let's say your data has a Date field with the date as example 31-7-2017

Temp: