Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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?
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.
Sorry, I've misread your question:
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)
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?
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];