Page 1 of 1
number of days in month for rule calc ?
Posted: Mon Feb 28, 2011 3:42 am
by BigG
Hi,
I have a date Starting beginning of financial year. We then Have a 'Budget Number of Months' measure that users enter number of months a project will run. A rule then calculates the end date based on this month entry.
Below is the rule I use. I would like to be more accurate in the month number of days as the 365/12 sometimes targets beginning of month and other times just before. Does anyone have a suggestion on how to recognise month number days OR at least round to the closest 1st day of next month?
Code: Select all
['End Date' ] = N: DayNo('2012-07-01') + 21916+ ['Number of Months' ]* 365/12;
thanks in advance
Re: number of days in month for rule calc ?
Posted: Mon Feb 28, 2011 3:59 am
by Goatship
You could try adding another element calculating No of day in a month.
If ((No of Month) = Month with 31 days, then 31, continue,
If ((No of Month) = Month with 31 days, then 31, continue,
If ((No of Month) = Feb, then 28, 0 )))
Then you put this element, No of day in a month, in the rule calculating End Date.
Re: number of days in month for rule calc ?
Posted: Mon Feb 28, 2011 4:07 am
by Alan Kirk
BigG wrote:Hi,
I have a date Starting beginning of financial year. We then Have a 'Budget Number of Months' measure that users enter number of months a project will run. A rule then calculates the end date based on this month entry.
Below is the rule I use. I would like to be more accurate in the month number of days as the 365/12 sometimes targets beginning of month and other times just before. Does anyone have a suggestion on how to recognise month number days OR at least round to the closest 1st day of next month?
Code: Select all
['End Date' ] = N: DayNo('2012-07-01') + 21916+ ['Number of Months' ]* 365/12;
thanks in advance
There are probably several ways you could do that.
Edit: I've just deleted my main suggestion because I realised it would have a problem crossing calendar year barriers; I'll come back to that later. But in short it would involve extracting the date components and adding them on to the start date.
Another way would be to use a dates lookup cube.
Another, doubtless simpler and more elegant, way will probably be suggested by someone else, since the two above are just off the top of my head.
Re: number of days in month for rule calc ?
Posted: Mon Feb 28, 2011 5:32 am
by Alan Kirk
Alan Kirk wrote:Edit: I've just deleted my main suggestion because I realised it would have a problem crossing calendar year barriers; I'll come back to that later. But in short it would involve extracting the date components and adding them on to the start date.
Sorry,
this is the sort of solution that I was thinking of:
Code: Select all
['End Date' ] = N: 21916+ DayNo ( DateS ( 2012+Int( (7 + ['Number of Months'] -1)/12 ), (7+['Number of Months'])-(INT((7+['Number of Months']-1)/12)*12) ,1) ) ;
In other words, you work out how many future years your month increment will take you into, and calculate the year value from that. (I've hard coded the value, but it would be possible to parse this from a start date if it's not fixed in the way your example was.)
You then work out which month of the future year it'll be by adding the number of months, and subtracting the year increment. Turn that into a date via DateS() and that into a serial date via dayno, add 21916 and you have your new Excel serial date.
It's entirely possible that there's a simpler way of doing this, and indeed the lookup cubes option would be much more straightforward (if in need of additional maintenance).
Re: number of days in month for rule calc ?
Posted: Mon Feb 28, 2011 6:00 am
by BigG
thanks for both the quick responses, I will try the last method in morning but after much fiddling I came up withthis rough and ready method... (which will probably change when I try yours). Note my date format for displaying results is MM-YYYY so I get away wit hthe code below (just make sure everything tips over 1 for next month if not already 1)
Code: Select all
#ADD MONTHS TO 1st July BUDGET YEAR
#If DAY greater than 25 (assuming never less than 25)
['End Date' ] = N: IF(DAY(DATE(DayNo('2012-07-01') + ['Number of Months' ]/12* 365)) >25,
#THEN add an EXTRA 5
DayNo('2012-07-01') + 21916+ ['Number of Months' ]/12* 365+5
#ELSE leave as is
,DayNo('2012-07-01') + 21916+ ['Number of Months' ]/12* 365)
;
Re: number of days in month for rule calc ?
Posted: Tue Mar 01, 2011 3:07 am
by BigG
sweet, cheers Alan, this is the final view
Code: Select all
['End Date' ] = N: 21916+
DayNo ( DateS ( DB('LU_Control', 'String', 'Start Year')
+Int( (7 + ['Number of Months'] -1)/12 ),
(7+['Number of Months'])-(INT((7+['Number of Months']-1)/12)*12) ,1) ) ;
Re: number of days in month for rule calc ?
Posted: Mon Feb 27, 2012 12:50 pm
by bigA
Hi All,
I had the same requirement to get future months date from a given point and was wondering why you needed to substract 1 as it seems like the same result with or without it.
['End Date'] =
21916 + dayno(dates(
numbr('2011')
+ int((7 + 12)/12),
(7 + 12) - (int((7 +12)/12)*12) ,1));
I could not figure out why we needed that in the logic ..Just wanted to know the reasoning behind it. Cheers
Re: number of days in month for rule calc ?
Posted: Mon Feb 27, 2012 7:51 pm
by lotsaram
Although doing the whole calculation via rules is possible my preferred solution and advice for big A and big G would be to create a lookup cube to return the end date from a start date which was Alan's second suggestion. This approach would both simplify the rules and perform better.
Re: number of days in month for rule calc ?
Posted: Tue Jun 02, 2015 2:44 pm
by 4rtem
I use following formula in TI where period is 'YYYY-MM':
vMonthLastDate=DATE(DAYNO(TIMST(DAYNO(pPeriod | '-15')+30,'\Y-\m') | '-01')-1,1);
The logic behind is to find first date of next month and deduct it by 1.