Dynamic Rolling Period 12 months

[Anonymous]

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!

[_k0zm0_2]

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;

[Anonymous]

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?

[niclaz79]

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.

[_k0zm0_2]

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)

[Anonymous]

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?

[niclaz79]

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];