topic Re: Dynamic Rolling Period 12 months in QlikView

Dynamic Rolling Period 12 months

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Dynamic Rolling Period 12 months

_k0zm0_2 — Fri, 11 Aug 2017 06:39:50 GMT

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;

Re: Dynamic Rolling Period 12 months

Anonymous — Fri, 11 Aug 2017 06:57:06 GMT

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?

Re: Dynamic Rolling Period 12 months

niclaz79 — Fri, 11 Aug 2017 07:36:55 GMT

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.

Re: Dynamic Rolling Period 12 months

_k0zm0_2 — Fri, 11 Aug 2017 07:39:24 GMT

Sorry, I've misread your question:

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)

Re: Dynamic Rolling Period 12 months

Anonymous — Fri, 11 Aug 2017 08:01:53 GMT

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?

Re: Dynamic Rolling Period 12 months

niclaz79 — Fri, 11 Aug 2017 08:18:57 GMT

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

You would load your data as:

Temp:

LOAD

AutoNumber(Year(Date), Month(Date)) as MonthIndex

Resident [DataTable]

Order by Year(Date) asc, Month(Date) asc;

Drop table [DataTable];